 |
|
Oracle: export procedures & triggers
Oracle Database Tips by Donald Burleson |
Question: How can I export and import stored
procedures, functions and trigger definitions?
Answer: The Oracle export utility (exp or expdp)
exports the data dictionary entities associated with an object,
and a full export grabs the entire data dictionary.
You can also join dba_objects into the dba_source
views to get the source for all stored procedures functions and
triggers. If you don't want to write your own dictionary
scripts, get my Oracle
script collection.
When using export, you can extract the trigger definitions by
specifying the table name and using the rows=n option in the
export parfile:
expdp fred/flintstone
tables=(fred), rows=n
Displaying stored procedure, function and trigger syntax is
trickier because the import utility (imp or impdp) does not
display dictionary objects in a display format. To display
dictionary entries with import, include the rows=n and
show=y parameters.
impdp fred/flintstone
tables=(fred), show=y, rows=n
You can also use the
dbms_metadata procedure to extract DDL and stored procedure,
function and trigger definitions. The dbms_metadata
utility is also used for punching source code, PL/SQL, and Java
that is stored inside Oracle packages. Stored procedures or
functions can also easily be extracted using dbms_metadata:
connect fred/flintstone;
spool procedures_punch.lst
select
dbms_metadata.GET_DDL('PROCEDURE',u.object_name)
from
user_objects u
where
object_type = 'PROCEDURE';
spool off;
