Generate DDL for Oracle Stored Procedure Dependency Graph

Question

With TOAD I know I can view the dependency (uses) graph of a stored procedure using the schema browser. And, the Oracle utility procedure deptree_fill can do something similar. What I want to do is script out all of the stored procedures, functions and table definition DLLs into a file that I can use to recreate those objects in another database. Is there a tool or an existing script for this purpose? My own searching has not found a solution. In my particular case the stored procedure uses a dozen other procedures, a few functions and twenty tables.

Edit 1

Maybe my original question was not clear. What I am looking for is something that will take the stored procedure I am interested in and script it and all of its dependency graph into one or more files.

The schema I am dealing with has hundreds of objects in it and the dependency graph has ~50 objects in it. So I'd rather not dig through large lists in TOAD or write an Oracle script myself if I can avoid it.

Solution 1

All sources can be extracted using the dbms_metadata package.

To get the source of a table:

select dbms_metadata.get_ddl('TABLE', 'SOME_TABLE')
from dual;

To get the source of a stored procedure:

select dbms_metadata.get_ddl('PROCEDURE', 'SOME_PROC')
from dual;

Using that you can create a SQL script that extracts everything and then spool the result to a file.

More details about the various functions in dbms_metadata can be found in the manual:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#i1015856

Solution 2

Hmm, it is quite easy to find in google. Get table DDL: How to get Oracle create table statement in SQL*Plus

Code of stored procedures can be found in table USER_SOURCE.

Also, for exporting schema to another DB you can use oracle utilities: http://docs.oracle.com/cd/B28359_01/server.111/b28319/exp_imp.htm#g1070082

Comments

[a_horse_with_no_name]

@dave: then your SQL client cuts off CLOB values. The code does work.