Generate DDL for a specific Oracle schema
Introduction
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:
- SQL*Plus system variables: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/SET-system-variable-summary.html#GUID-A6A5ADFF-4119-4BA4-A13E-BC8D29166FAE
- GET_DDL function from DBMS_METADATA package: https://docs.oracle.com/database/121/ARPLS/d_metada.htm#ARPLS66877
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;
/