.NET: How to retrieve the body of an Oracle 9i PL/SQL procedure or function

Question

DISCLAIMER: Let's just say I have a pet project to satisfy my insane desire to write a decent Oracle client that isn't based on Java, while appeasing my coding hobby. END OF DISCLAIMER

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.

Unfortunately, Google has been less than helpful in this regard, and StackOverflow's search feature (to my overwhelming dismay) has been sorely disappointing.

Come on, guys. Don't let me down! Is there a way to do this?

Answer 1

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>

Answer 2

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