Source code for specific stored procedure or function

Question

I can use all_arguments and all_procedures to list the procedures and functions inside any given package and with DBMS_METADATA I can extract the DDL for that package. Is there an easy way (other than lots of instring and substring calls) to obtain the procedure or function source code separately for each separate block of code in a package.

Something like this:

Owner | Package Name | Object Name | Overload | Arguments | Source

Obviously using substring and instring will present issues with overloaded functions.

All_arguments has the subprogram_id field which according to the very sparse documentation on it looks like it does uniquely reference which procedure it related to in the package but there doesn't appear to be anything that uses it.

Cheers in advance

Answer 1

IIRC, PLSQL allows nested packages and functions. In this case, you'll find that "instring" and "substring" may not be adequate to extract the source code, as you're facing recursion, and string functions typically only handle a smaller class of computations (typically regular expressions). This is a classic problem people have trying to parse languages with simple string manipulation. You can get around limits of string functions by essentially hacking to produce a poor man's parser but this can be a surprising amount of work if you want it to be deadly right, because you have to handle at least the recursive grammar rules that matter for your extraction.

Another way to get reliable access to the elements of a PLSQL package is to use a language parser. The DMS Software Reengineering Toolkit has a full PLSQL parser.

You'd have to extract the package text to a file first, and then apply the PLSQL parser to it; that produces an abstract syntax tree (AST) internally in the parser. Given the name of a function, it is rather easy to search the AST for the function with a matching name. You'd end up with more than one hit if you have overloaded functions; you might qualify the function by the hierarchy in which it is embedded or the information about the arguments that you might have. Having identified a specific function in the AST, one can ask DMS to pretty-print that tree, and it will regenerate the text of (complete with comments) for that function.