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

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

Dig Deeper on Oracle database administration

Data Management
Business Analytics
  • Why WebAssembly? Top 11 Wasm benefits

    Latency and lag time plague web applications that run JavaScript in the browser. Here are 11 reasons why WebAssembly has the ...

  • Why Java in 2023?

    Has there ever been a better time to be a Java programmer? From new Spring releases to active JUGs, the Java platform is ...

  • How developers can avoid remote work scams

    Software developers can find good remote programming jobs, but some job offers are too good to be true. Follow these tips to spot...

Data Center
Content Management