This documentation is not maintained. Please refer to doc.castsoftware.com/technologies to find the latest updates.


On this page:

Summary: This page explains how to generate DDL for an Oracle schema using the SQL*Plus tool, for use with the SQL Analyzer extension.

Oracle official documentation:

Extraction example

DDL extraction for a specific schema

set termout off 
set verify off 
set trimspool on 
set linesize 32767
set longchunksize 200000 
set long 200000 
set pages 0
set serveroutput on
set feedback off
spool TEST_SCHEMA_DDL.sql

SQL> select dbms_metadata.get_ddl(object_type, object_name, owner) || chr(10) ||'/'|| chr(10) || chr(10)
  2  from
  3  (
  4      --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
  5      select
  6          owner, object_id,
  7          object_name,
  8          decode(object_type,
  9              'PACKAGE',            'PACKAGE_SPEC',
 10              'PACKAGE BODY',       'PACKAGE_BODY',
 11              'TYPE',               'TYPE_SPEC',
 12              'TYPE BODY',          'TYPE_BODY',
 13              'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',
 14              object_type
 15          ) object_type
 16      from dba_objects
 17      where owner in ('TEST')
 18          --These objects are included with other object types.
 19          and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
 20             'LOB','LOB PARTITION','LOB SUBPARTITION','TABLE PARTITION','TABLE SUBPARTITION', 'JOB','TABLESPACE')
 21          --Ignore system-generated types that support collection processing.
 22          and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
 23          --Exclude nested tables, their DDL is part of their parent table.
 24          and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
 25          --Exclude overflow segments, their DDL is part of their parent table.
 26          and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
 27  )
 28  order by owner, object_id, object_name;
spool off;

DDL extraction for a specific list of packages from a specific schema

In the previous example, just add the filter you need, e.g. : for a specific owner you want to extract a limited list of packages :

set termout off 
set verify off 
set trimspool on 
set linesize 32767
set longchunksize 200000 
set long 200000 
set pages 0
set serveroutput on
set feedback off
spool TEST_SCHEMA_PKG_DDL.sql

SQL> select dbms_metadata.get_ddl(object_type, object_name, owner) || chr(10) ||'/'|| chr(10) || chr(10)
  2  from
  3  (
  4      --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
  5      select
  6          owner, object_id,
  7          object_name,
  8          decode(object_type,
  9              'PACKAGE',            'PACKAGE_SPEC',
 10              'PACKAGE BODY',       'PACKAGE_BODY',
 11              'TYPE',               'TYPE_SPEC',
 12              'TYPE BODY',          'TYPE_BODY',
 13              'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',
 14              object_type
 15          ) object_type
 16      from dba_objects
 17      where owner in ('TEST')
 18          --These objects are included with other object types.
 19          and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
 20             'LOB','LOB PARTITION','LOB SUBPARTITION','TABLE PARTITION','TABLE SUBPARTITION', 'JOB','TABLESPACE')
 21          --Ignore system-generated types that support collection processing.
 22          and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
 23          --Exclude nested tables, their DDL is part of their parent table.
 24          and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
 25          --Exclude overflow segments, their DDL is part of their parent table.
 26          and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
 27  )
 28 where owner = 'TOTO' and object_name in ('PACKAGE_1', 'PACKAGE_2') 
 29  order by owner, object_id, object_name;
spool off;

Check if your code is valid SQL

For example, the following is not a valid SQL: the line size is fixed at 80 characters and the SQL*Plus prompt, SQL>, and commands, select and spool off, should be removed. 

SQL*Plus alike script - NOK
SQL> 
SQL> select dbms_metadata.get_ddl(object_type, object_name, owner) || chr(10) ||'/'|| chr(10) || chr(10)
  2  from
  3  (
  4      --Convert DBA_OBJECTS.OBJECT_TYPE to DBMS_METADATA object type:
  5      select
  6          owner, object_id,
  7          object_name,
  8          decode(object_type,
  9              'PACKAGE',            'PACKAGE_SPEC',
 10              'PACKAGE BODY',       'PACKAGE_BODY',
 11              'TYPE',               'TYPE_SPEC',
 12              'TYPE BODY',          'TYPE_BODY',
 13              'MATERIALIZED VIEW',  'MATERIALIZED_VIEW',
 14              object_type
 15          ) object_type
 16      from dba_objects
 17      where owner in ('TEST')
 18          --These objects are included with other object types.
 19          and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION',
 20             'LOB','LOB PARTITION','LOB SUBPARTITION','TABLE PARTITION','TABLE SUBPARTITION', 'JOB','TABLESPACE')
 21          --Ignore system-generated types that support collection processing.
 22          and not (object_type = 'TYPE' and object_name like 'SYS_PLSQL_%')
 23          --Exclude nested tables, their DDL is part of their parent table.
 24          and (owner, object_name) not in (select owner, table_name from dba_nested_tables)
 25          --Exclude overflow segments, their DDL is part of their parent table.
 26          and (owner, object_name) not in (select owner, table_name from dba_tables where iot_type = 'IOT_OVERFLOW')
 27  )
 28  order by owner, object_id, object_name;                                                         
                                                                                
                                                                                
  CREATE OR REPLACE EDITIONABLE FUNCTION "TEST"."TEST_FUNCTION"         ( Val1 PLS
_INTEGER, Val2 PLS_INTEGER )                                                    
                RETURN PLS_INTEGER                                              
IS                                                                              
        TESTVal1 PLS_INTEGER := Val1;                                            
        TESTVal2 PLS_INTEGER := Val2;                                            
        RetVal PLS_INTEGER := 0;                                                
        BitNum PLS_INTEGER := 0;                                                
BEGIN                                                                           
        WHILE TESTVal1 > 0 or TESTVal2 > 0 LOOP                                   
                RetVal := RetVal + LEAST(MOD(TESTVal1, 2), MOD(TESTVal2, 2)) * POW
ER(2, BitNum);                                                                  
                                                                                
                TESTVal1 := TRUNC(TESTVal1/2);                                    
                TESTVal2 := TRUNC(TESTVal2/2);                                    
                BitNum := BitNum + 1;                                           
                                                                                
        END LOOP;                                                               
                                                                                
        RETURN RetVal;                                                          
                                                                                
END;
/

SQL> 
SQL> spool off 

The correct script should look like this:

SQL alike script - OK
  CREATE OR REPLACE EDITIONABLE FUNCTION "TEST"."TEST_FUNCTION"         ( Val1 PLS_INTEGER, Val2 PLS_INTEGER )                                                    
                RETURN PLS_INTEGER                                              
IS                                                                              
        TESTVal1 PLS_INTEGER := Val1;                                            
        TESTVal2 PLS_INTEGER := Val2;                                            
        RetVal PLS_INTEGER := 0;                                                
        BitNum PLS_INTEGER := 0;                                                
BEGIN                                                                           
        WHILE TESTVal1 > 0 or TESTVal2 > 0 LOOP                                   
                RetVal := RetVal + LEAST(MOD(TESTVal1, 2), MOD(TESTVal2, 2)) * POWER(2, BitNum);                                                                  
                                                                                
                TESTVal1 := TRUNC(TESTVal1/2);                                    
                TESTVal2 := TRUNC(TESTVal2/2);                                    
                BitNum := BitNum + 1;                                           
                                                                                
        END LOOP;                                                               
                                                                                
        RETURN RetVal;                                                          
                                                                                
END;
/