Home    HRIS    Oracle DBMS_METADATA Package

Oracle DBMS_METADATA Package

By / September 17, 2009 / / 0 Comments

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.