Use an Oracle API to Extract and Store Database Objects' DDL-3 : Page 3

The Metadata Memory Repository: MetaDataPkg Package Specification
First, you need to create a record type to store all of the necessary information for individual objects, like tables, indexes, etc:

subtype tString       is varchar2(30);
subtype tDBString     is varchar2(255);
subtype tDBLongString is varchar2(4000);
subtype tLongString   is varchar2(32767);

type tArrayLongString is table of tLongString
index by pls_integer;

type tMetaObject is record
(
   aName         tString,
   aType         tString,
   aLogging      tString,
   aParallel     tString,
   aStatus       tString,
   aValidated    tString,
   aRely         tString,
   aDDLString    tLongString
);

tMetaObject attributes hold the following information:

• aName: object's name, i.e. EMPTEST_PK1.
• aType: object's type, i.e. 'YES' (partitioned)/'NO' (partitioned) (for tables), 'UNIQUE'/'NONUNIQUE' (for indexes), constraint type 'P'/'U'/'C'/'R' (for constraints)
• aLogging: object's logging option, i.e. 'LOGGING'/ 'NOLOGGING' (for tables and indexes).
• aParallel: object's degree of parallelism (for tables and indexes).
• aStatus: object's status, i.e. 'VALID'/'UNUSABLE' for indexes, 'Y' (backed-up)/'N' (nonbacked-up) for tables.
• aValidated: object's validate option, i.e. 'VALIDATED'/'NOT VALIDATED' (for constraints).
• aRely: object's rely option, i.e. 'RELY'/'NORELY' (for constraints).
• aDDLString: object's definition SQL string.

Now you need to define the associative array type to be able to instantiate the object of that type so it holds multiple objects of the tMetaObject type, i.e. all EmpTest indexes:

type tArrayMetaObject is table of tMetaObject
index by pls_integer;

Next, you need to create a record type that consists of one tMetaObject attribute for the table itself (aTable) and three tArrayMetaObject attributes: one for indexes (aIndexes), one for constraints (aConstraints), and one for triggers (aTriggers):

type tFullMetaObject is record
(
   aTable       tMetaObject,
   aIndexes     tArrayMetaObject,
   aConstraints tArrayMetaObject,
   aTriggers    tArrayMetaObject
);

The object of type tFullMetaObject holds all the objects' metadata for a single table. Finally, the top level type is the associative array of tFullMetaObject

type tArrayFullMetaObjectByString is table of tFullMetaObject index by varchar2(30); The above type's object serves as a metadata memory repository and holds the complete set of metadata information for multiple tables.

All of the above types are contained in the MetaDataPkg package specification (Listing 2). I've also introduced the following API's:

• MetaDataPkg.Load() Procedure: Loads metadata information into the memory repository of a specified table.
• MetaDataPkg.GetMeta() Function: This retrieves the tFullMetaObject type's object from the memory repository.
• MetaDataPkg.SetMeta() Procedure (overloaded): Stores the object's metadata in the memory repository.
• MetaDataPkg.Reset Procedure (overloaded): Resets the memory repository.
• MetaDataPkg.Show Procedure (overloaded): Displays the content of the memory repository.

Previous Page

Next Page