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

Copying stored procedures to another database

I want to copy store procedures to another user's database.

I want to copy store procedures to another user's database. How should I do it?

If you are using Oracle 9i or higher, then you can use the DBMS_METADATA.GET_DDL procedure to reverse engineer the CREATE PROCEDURE statement similar to the following:

SELECT DBMS_METADATA.GET_DLL('PROCEDURE','MY_PROC','SCOTT') FROM dual;
Output from the above will be a CREATE PROCEDURE statement that you can store in a text file and run in the next database.

If you are using Oracle 8i or earlier, then you will have to create the CREATE PROCEDURE command yourself. Luckily, all of the source code for the stored procedure can be viewed in DBA_SOURCE.

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close