how to print out the definition of a procedure in Oracle?

Question

Is there a way in oracle to see what a procedure's structure is? I'm trying to log and am running procedures and wanted to store the actual procedure structure in my log.

Answer 1

You could query the ALL_SOURCE table

SELECT text 
  FROM all_source
 WHERE owner = <<owner of procedure>>
   AND name  = <<name of procedure>>
 ORDER BY line

If you are dealing with a procedure that is inside a package

SELECT text 
  FROM all_source
 WHERE owner = <<owner of procedure>>
   AND name  = <<name of procedure>>
   AND type  = 'PACKAGE BODY'
 ORDER BY line

will give you the text of the package body. You could also get the text of the package specification using a TYPE of "PACKAGE"

Answer 2

SELECT TEXT, LINE FROM ALL_SOURCE WHERE 
    NAME = UPPER('$name') -- the table also has an owner field to track the user
    ORDER BY TYPE, -- type is generally procedure, but there are functions and 
                   -- more complex structures as well, such as PACKAGE
    TO_NUMBER( LINE )

Answer 3

dbms_metadata package, get_ddl function, perhaps?

SELECT dbms_metadata.get_ddl('PROCEDURE','<yourproc>','<schema>') FROM dual;