Code that Does All of the Work
Some comments about MetaDataPkg
package body code (Listing 3
). The private procedure SetEnvironment()
contains all the environment setup code. The procedure is called from the package initialization section. Therefore, it only executes once per session which is all you need. You want to set it up once at the very beginning. The DBMS_METADATA
package provides the API for setting the environment parameters: DBMS_METADATA.SET_TRANSFORM_PARAM()
The following code prevents the output from formatting with indentation and line feeds:
dbms_metadata.session_transform, 'PRETTY', false);
The next three lines emit segment attributes (physical attributes, storage attributes, tablespace, logging, etc.), storage and tablespace clauses for tables, and indexes' object definitions:
dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', true);
dbms_metadata.session_transform, 'STORAGE', true);
dbms_metadata.session_transform, 'TABLESPACE', true);
It's important to specify all of the physical, storage, and logging attributes explicitlyotherwise, they'll be set to defaults that may differ from the originally set values.
The last three lines of the SetEnvironment() procedure prevent all of the non-referential and referential constraints from being included in the table's DDL. It also suppresses emitting table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements:
dbms_metadata.session_transform, 'CONSTRAINTS', false);
dbms_metadata.session_transform, 'REF_CONSTRAINTS', false);
dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', false);
For maximum flexibility, extract object definitions for tables, indexes, and constraints separately and keep them separate. That way you control the order in which they are created.
MetaDataPkg package's main workhorse is the MetaDataPkg.GetDDL() function. MetaDataPkg.GetDDL() contains an extended version of the code from the Listing 1. What's been added is the ability to extract DDL strings longer than 32767 characters. This helps to handle partitioned table definitionswhich can get rather long when the number of partitions grows. That's why the GetDDL() code parses and loads the DDL string into the vLongStrings array of strings up to 32767 characters each. The current code version only returns the first array's element, so you need to modify the code and make the array an attribute of tMetaObject record type. This allows it to handle DDL strings longer than 32767 characters, which is rather rare.
Use the MetaDataPkg.GetMeta() API to get complete metadata objects per specified
table. This API accepts two parameters: pTable, which is the table name, and pForce, the Boolean flag. When the pForce flag is set to TRUE, it forces metadata retrieval from Oracle dictionaries and then loads the metadata into the memory repositorywhether or not it's there already. However, the default value is FALSE, so the first call loads metadata into the memory repository and returns the object of tFullMetaObject type. Any subsequent GetMeta() calls simply retrieve the metadata from the repository.
Using the MetaDataPkg Package
To illustrate how to use the MetaDataPkg package, I created a small anonymous block code. It loads the medatada of EmpTest table in to the metadata repository and then prints it's content.
This is the anonymous PL/SQL block:
vTable MetaDataPkg.tString := 'EmpTest';
vRunStartTime := dbms_utility.get_time;
dbms_output.put_line('Time Elapsed: ' ||
to_char((dbms_utility.get_time - vRunStartTime) / 100) || ' sec.');
shows the output produced by the above code.
As you can see, this code loads complete metadata information for the EmpTest table and its indexes and constraints into the memory repository and retrieves it in less than a second. You now have the API's to develop an automated solution for any data manipulation that involves renaming, swapping, and dropping database objects. In my next article, I'll talk about using the MetaDataPkg package for a high-performance, massive DELETE operation.