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

SearchDataManagement
SearchBusinessAnalytics
SearchSAP
SearchSQLServer
TheServerSide.com
SearchDataCenter
SearchContentManagement
SearchHRSoftware
Close