Browse DevX
Sign up for e-mail newsletters from DevX


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




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date