SELECT dbms_metadata.get_ddl('FUNCTION','MY_FUNC','SCOTT') FROM dual;
The first parameter denotes the object type. Valid values are FUNCTION, PROCEDURE, TRIGGER, PACKAGE, TABLE, VIEW, SEQUENCE, etc. The second parameter is the object name and the third parameter is the object's owner.
You can use the Data Dictionary to make this work even quicker. For instance, all of my triggers and procedures can be seen in the USER_OBJECTS view. So I can use the following:
SELECT dbms_metadata.get_ddl(object_type,object_name,owner) FROM user_objects WHERE object_type IN ('FUNCTION,'PROCEDURE);
In SQL*Plus, you can write the output from the above into a text file with the SPOOL command. To start spooling to a file and turn off spooling, use the following:
SPOOL my_file_name SPOOL OFF
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.