Exporting selected procedures and functions in Oracle

How can we export selected procedures/functions/packages in Oracle?

How can we export selected procedures/functions/packages in Oracle?
The best way is to use the DBMS_METADATA.GET_DDL function to return the CREATE statements to re-create these object. Then spool the results to a file so that these can be run on your target database. You can generate the CREATE statement for a function similar to the following:
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 administration

Data Management
Business Analytics
SearchSAP
TheServerSide.com
Data Center
Content Management
HRSoftware
Close