Home arrow Oracle DDL Extraction

Overview (Version

The provided scripts will extract all sources from an Oracle schema and spool them into the filesystem in a specific structure. The scripts leverage the functionality of the Oracle Package DBMS_METADATA and thus will respect the specifics of later Oracle versions as well.

These scripts are used in different actual development projects for my clients. These are an important building block for my concepts related to configuration and lifecycle management of an Oracle APEX application, especially with regard to the SQL and PL/SQL related parts. These concepts and principles are described in chapter 9: Lifecylce Management from the book Expert Oracle Application Express.
Expert Oracle Application Express

How does it work?

1. Installation of required objects in the schema [application schema]

First of all we need to install a few objects into our application schema:
  • Sequence XLIB_SEQ
  • Table XLIB_DDL_STATEMENTS, will store the DLL statements generated by DBMS_METADATA
  • Helper Function XLIB_DDL_GET_COLUMN_LIST to return a concatenated list of columns for a table.
The installation procedures are described in detail on the installation page.

2. Extract the sources and store them in an intermediate table

The DDL for all objects from the connected schema [application schema] are extracted via the DBMS_METADATA package and stored in the table XLIB_DDL_STATEMENTS in the application schema.

3. Spool the objects into the filesystem using sqlplus

Using sqlplus the DDL statements from the table XLIB_DDL_STATEMENTS are spooled into the filesystem. The filesystem structure is initialized by the supplied ant script (build.xml), see http://ant.apache.org/ for details.

Directory Structure

This process can be influenced by
  • Deleting objects from the table XLIB_DDL_STATEMENTS before spooling them into the filesystem (see file custom_export.sql).
  • Change the definition of the view XLIB_DDL_EXPORT_V, it determines which object types are grouped together and spooled into which files. See file spool_object_groups.sql for details, the view XLIB_DDL_EXPORT_V is created in there on the fly.
  • Change the ant script build.xml in order to change the directories in which the files will be stored.

Current restrictions

We certainly use specific constraints in our projects, thus we have adapted the scripts to our needs and didn't implement a more generic. Our constraints are the following ones:
  • Identical tablespace names in the development, test and production environments. We extract the DDL for all objects, remove the absolute schema reference (so that you can install the objects in a different schema) but don't change the tablespace name. 
  • This is the first release of the scripts as we currently use them in our projects. The filenames and contents differ slightly from what is explained in the book.

Enhancements / Further Development

Let me know what is working and what not, tell me your enhancement requests. Drop me a note at dietmar.aust@opal-consulting.de.