How can I get DDL for a given Oracle package's "public" procedures?

Question

I have a large number of Oracle packages. I'd like to write a query that allows me to create a list of where a handful of common problems can be found within those packages.

What I've tried so far

• I know I can isolate package arguments by querying dba_arguments
• I know I can find lines of code by querying dba_source
• I know I can identify procedures that appear on a package's spec by querying DBA_Procedures

I can join the first two sets of results together, but I'm having trouble joining the third set of results.

How can I get DDL for procedures that appear on a given package's spec?

Answer 1

Try this one:

select dbms_metadata.get_ddl ('PACKAGE', 'YOURPACKAGENAME', 'YOURSCHEMA') from dual;