Skip to Main Content
  • Questions
  • where to use metadata table as a plsql developer

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, kanaka.

Asked: January 31, 2018 - 10:58 am UTC

Last updated: February 01, 2018 - 3:15 am UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

Hii friends,

how, when, where to use metadata views like ALL_SOURCE, USER_SOURCE, ALL_DEPENDENCIES, USER_DEPENDENCIES, ALL_OBJECTS,
USER_OBJECTS.

THANKS.

and Connor said...

Um...... when you need to.

eg #1, I'm about to drop a table - I might query ALL_DEPENDENCIES to see what impact this might have.

eg #2, I'm not convinced my source code control system is in sync with my database. I could compare USER_SOURCE for some objects with what I have in source code system.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 01, 2018 - 3:17 am UTC


For a package that generates itself

Marcus, February 01, 2018 - 8:03 am UTC

One of our packages generates itself from a table of named exceptions and its own source code
https://livesql.oracle.com/apex/livesql/file/content_CCDPAOR8IXKYVUT9YHMCZ7W3U.html
This way it is easy to have a central place to define named exceptions.

And each object has in its header comment the version number in a form like CM#102. So if you want to check the object versions you can simply ask the database
SELECT  s1.name  "Name"
       ,s1.type  "Typ"
       ,SUBSTR(REGEXP_SUBSTR(s1.text,'CM#[[:digit:]]*'),4)  "Version"
FROM    user_source s1
WHERE   s1.line = ( SELECT  MAX(s2.line)
                    FROM    user_source s2
                    WHERE   INSTR(s2.text,'CM#') > 0
                    AND     s2.name = s1.name
                    AND     s2.type = s1.type
                    )
ORDER BY s1.name
        ,s1.type

Name           Typ          Version
-----------------------------------
ScanFolder     JAVA SOURCE  1
SEPIS$SENDMAIL PACKAGE      6
SEPIS$SENDMAIL PACKAGE BODY 9
...


Or if you want to search in your code for occurrences of <searchstring>
Or if you lost the source code of a procedure you can simply spool it into a file
Or ...

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library