Oracle DBMS_METADATA Package
Posted by Matt Barker | Filed under Oracle
A recent archiving exercise for a client saw the need to recreate a number of objects in the database.
You could search through an find the object definitions in your source code, then cut and paste to recreate the objects in the correct order. But why not make Oracle work for you and use the DBMS_METADATA package generate the object definitions for you dynamically!
The DBMS_METADATA package allows you to extract the DDL required to recreate any object (tables, triggers, indexes etc) in the database that you have access to.
So, should we wish to extract the DDL required to generate the indexes on the EMPLOYEES table in the Oracle XE Database HR schema, we can run the following PL/SQL:
set serveroutput on size 100000
declare
handle number;
tranform_handle number;
object_definition clob;
begin
-- Define the object type we're exporting
handle := dbms_metadata.open(upper('INDEX'));
-- only get the objects for the current table
dbms_metadata.set_filter(handle, 'BASE_OBJECT_NAME', 'EMPLOYEES');
-- add the trailing semicolon to the output
tranform_handle := dbms_metadata.add_transform(handle, 'DDL');
dbms_metadata.set_transform_param(tranform_handle, 'SQLTERMINATOR', TRUE);
-- extract all the object of type for the given table
loop
object_definition := dbms_metadata.fetch_clob(handle);
exit when object_definition is null;
--convert the clob to a string
dbms_output.put_line(dbms_lob.substr(object_definition, 32000, 1));
end loop;
end;
Obviously in a real life example you wouldn’t sent the output to the screen, but this simple example shows another of the useful built in packages provided by Oracle.
For more information on the DBMS_METADATA package, see the Oracle online reference guide.
Tags: dbms_metadata, Oracle, sql
