DDL Objects in ORACLE

Published by Patrizia Gardis on 6 March 20196 March 2019

In the DDL Objects in ORACLE article, we have the ability to extract the DDL (Data Definition Language) of objects.

The DDL of an object is the code by which the object is defined. In some cases, it is necessary to extract the creation code to verify the complete information with which the object was created.

So, when we need to extract the creation code of any object, we can proceed with extraction through the Oracle package dbms_metadata.get_ddl. If you wish to delve deeper into the topic, I direct you to the official Oracle DBMS_METADATA page.

This is the manual mode for extracting the creation code.

In addition to this method, it is possible to extract the creation code of objects through graphical tools such as SQL Developer or with TOAD, by going to the object in the Script tab.

Extraction DDL Oracle objects TABLE:

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('TABLE','&TABLE_NAME','&user') from dual;

Extraction DDL INDEX:

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('INDEX','&INDEX_NAME','&OWNER') from dual;

We can extract Oracle Views, which are queries stored in the database to speed up data retrieval.

Extraction DDL VIEW:

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('VIEW','&VIEW_NAME','&OWNER') from dual;

Subsequently, we can leverage the Oracle DBMS_METADATA package to extract entire software programs within the database as follows:

Extraction DDL PACKAGE:

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('PACKAGE','&PACKAGE_NAME','&OWNER') from dual;

Extraction DDL PROCEDURE:

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('PROCEDURE','&PROCEDURE_NAME','&OWNER') from dual;

Finally, we also utilize the same Package for TRIGGER and FUNCTION.

Extraction DDL TRIGGER:

set heading off;
set echo off;
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('TRIGGER','&TRIGGER_NAME','&OWNER') from dual;

Extraction DDL FUNCTION:

set heading off;
set echo off;
set lines 210
Set pages 999;
set long 90000;
select dbms_metadata.get_ddl('FUNCTION','&FUNCTION_NAME','&OWNER') from dual;

In addition to these objects, there are other articles on the site where the method for extracting DDL is indicated, for example, on creating the user with its ROLES and PRIVILEGES of reference.

To view my guides on CRS Command click here!
At this LINK, instead, you’ll find the SQL queries.

Stay updated on our activities by joining our community (remember to like our pages, let’s get in touch, and stay informed about all our new guides!).
Facebook
Youtube
Linkedin

• DDL Objects in ORACLE