PL/SQL Tip of the Month

The PL/SQL Tip of the Month is a regular feature of the "PL/SQL Pipeline", a free internet community for Oracle PL/SQL development. The PL/SQL Pipeline is hosted by author Steven Feuerstein, and sponsored by RevealNet. http://www.revealnet.com/pipeline.htm

May's Tip of the Month

What Programs Are Defined in My Package?

Have you ever wondered how you can get a list of the names of the procedures and functions in your package? The data dictionary does not, unfortunately, offer a view that contains the list of elements defined in a package specification (that would be so incredibly useful!). There are, however, a couple of ways to get at this information.

If you are using Oracle8, you can now "describe" a package with the DESCRIBE command. So if emp_pkg is the name of a package, you can issue this command:

SQL> describe emp_pkg

And you will then see scrolling on your screen all the program units of the package and their parameters. You will also probably notice some "junk"; there is a bug in the program that extracts this information, causing extraneous characters to appear.

The other option, and one that will work both in Oracle7 and Oracle8, was offered by Solomon Yakobson (syakobson_at_erols.com) in the "Pipetalk" section of the PL/SQL Pipeline:

SELECT DISTINCT procedure$
  FROM sys.argument$, dba_objects
 WHERE obj# = object_id
   AND object_name = 'YOUR_PACKAGE_NAME';

In this case, he is taking advantage of the data dictionary view that contains all the arguments for the procedures and functions, and then applying DISTINCT to show only the program names (the DBMS_DESCRIBE package relies on this same view to return the argument information in a series of PL/SQL tables).

Thanks, Solomon!

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue May 05 1998 - 08:50:30 CDT