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
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