WEBINAR:
On-Demand
Application Security Testing: An Integral Part of DevOps
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.