How to retrieve the body of an Oracle procedure or function

Question

What I'd like to be able to do is retrieve the schema information for subprograms, functions, package specifications and package bodies from an Oracle 9i database so that I can present them to the user in a C# client using the classes in the System.Data.OracleClient namespace.

So far, I've been able to display the high level schema data far faster than Java applications can, but the packages and functions are beyond my grasp. I can show the columns, their types, the indexes, table- and column level comments, and all sorts of really useful information in really useful ways. Now, if I could just get to the procedures.

Answer 1

Query the data dictionary table ALL_SOURCE http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2124.htm#1300946

Answer 2

Does this help? Not clear whether you wanted to get this via System.Data.OracleClient or via SQL?

SELECT TEXT
FROM   ALL_SOURCE
WHERE  NAME = <proc_name>
AND    OWNER = <schema>