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.
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.
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.