CODEWITHSUNDEEP
oracle-databaseplsqlextractsqlplusddl
bigD
bigD

Reputation: 1

Extracting PL/SQL procedure DDL using SQL*Plus in Oracle?

Is there a way to spool/extract/dump Oracle object's source DDL using only SQL*Plus?

I'm aware of dbms_metadata.get_ddl. Can you give me an example of how it can be done in SQL*Plus without resorting to any scripting language?

I assume that script will accepts object list or regexp and login string and result is a list of files on filesystem containing all the sources (one DDL per file).

Upvotes: 0

Views: 5571

Answers (1)

atokpas
atokpas

Reputation: 3351

Here is one example for you. 

SQL> set long 10000
SQL> spool get_ddl.txt
SQL> select dbms_metadata.get_ddl('PROCEDURE','INSERT_NEW','ADMIN') from dual;

DBMS_METADATA.GET_DDL('PROCEDURE','INSERT_NEW','ADMIN')
--------------------------------------------------------------------------------

  CREATE OR REPLACE PROCEDURE "ADMIN"."INSERT_NEW" (IN_SCHEMA_NAME varchar2, IN_
TABLE_NAME varchar2)
AS
BEGIN
dbms_output.put_line('INSERTED ' || IN_SCHEMA_NAME || '.' || IN_TABLE_NAME );
INSERT INTO TABLES_PKEYS (SCHEMA_NAME,TABLE_NAME,COLUMN_NAME,PKEY_INDEX)SELECT c
ons.owner,cols.table_name,cols.column_name,cols.position FROM all_constraints co
ns, all_cons_columns cols WHERE cols.owner=IN_SCHEMA_NAME AND cols.TABLE_NAME=IN
_TABLE_NAME AND cons.constraint_type = 'P' AND cons.constraint_name =cols.constr
aint_name AND cons.owner = cols.owner;

DBMS_METADATA.GET_DDL('PROCEDURE','INSERT_NEW','ADMIN')
--------------------------------------------------------------------------------
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('ERROR WHEN INSERTING ' || SQLERRM );
END INSERT_NEW;


SQL> spool off

[oracle @testsrv Desktop]$ cat get_ddl.txt

For details DBMS_METADATA

Upvotes: 1

Related Questions