Q
Problem solve Get help with specific problems with your technologies, process and projects.

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 backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close