Tapping into Oracle's Metadata - Part 2

James Koopmann's picture
articles: 

In this article James continues to explore the Oracle's Metadata API and provides a powerful function to compare objects and schemas and print the DDL required to bring them in sync.

In Part I of this two part series we explored how to use simple SELECT statements with the DBMS_METATA API. This simple ad hoc interface is great for just doing quick lookups into the metadata, verifying structures, and possibly rebuilding some objects down the line. That particular interface is meant to be a very simplistic method that is great for tinkering with the DDL in your database. Unfortunately it does not address some of the more sophisticated DDL manipulation that we often want to do. Such as creating a certain set of DDL statements from one schema into another. Or comparing two schemas to see where differences are. To really dive into the full power of the DBMS_METADATA API that lets you manipulate DDL you need have to venture past these simple queries and put some application logic around it.

This article does just that. It shows some of the minor differences in how to construct PL/SQL around the API calls to get at and manipulate the DDL extracted from the DBMS_METADATA API. This article takes on the task of comparing the tables in two different schemas to see where the differences are. Keep in mind this example could quickly be converted to compare any types of objects or you could extend the code to accept the object types to compare. Either way I am sure you will see the simplicity and power behind this form of compare that does not require you to get deep into the DBA_ views.

Ever since being introduced to table functions I really like this form of interface to PL/SQL logic. They provide an easy interface and produce output that mimics normal table output that can be used in a variety of ways. Plus it seems that Oracle themselves have adopted this form of output for a lot of their internal API type calls. To do this we need to first setup an abstract data type. This data type is what mimics a typical table in Oracle. There is no data stored in this object and is only an interface to the function call behind it. Go ahead and create these two TYPES and then issue a DESCRIBE on them to verify their structures.

CREATE TYPE tableddl_ty AS OBJECT
       (table_name      VARCHAR2(30),
        orig_schema     VARCHAR2(30),
        orig_ddl        CLOB,
        comp_schema     VARCHAR2(30),
        comp_ddl        CLOB)
/
CREATE TYPE tableddl_ty_tb AS TABLE OF tableddl_ty
/

Now on to the function call for comparing our two schemas. Here is the procedure. Please read the comments on the particular calls to the DBMS_METADATA package for a reference. This function will take as INPUT a reference cursor of objects to compare. Its input is the table(s) to compare along with an ORIG_SCHEMA (the baseline schema) and COMP_SCHEMA (the schema to compare against the baseline). If there is a difference between the two schemas or an object that exists in the ORIG_SCHEMA that is not in COMP_SCHEMA the function will return a row of output through the abstract data type (in table format) that shows the DDL for the two in a tabular format that we all have grown to love.

CREATE OR REPLACE FUNCTION tableddl_fc
        (input_values sys_refcursor)
         RETURN tableddl_ty_tb PIPELINED IS
         PRAGMA AUTONOMOUS_TRANSACTION;

  /* variables to be passed in by sys_refcursor */
  /* An example will be shown at the end of the article on how to pass in a reference cursor */
  table_name     VARCHAR2(30);
  orig_schema    VARCHAR2(30);
  comp_schema    VARCHAR2(30);

  /* setup output record of TYPE tableddl_ty */
  out_rec      tableddl_ty
            := tableddl_ty(NULL,NULL,NULL,NULL,NULL);

  /* setup handles to be used for setup and fetching metadata information            */
  /* handles are used to keep track of the different objects (DDL) we will be        */
  /*   referencing in the PL/SQL code                                                */
  hOpenOrig0     NUMBER;
  hOpenOrig      NUMBER;
  hOpenComp      NUMBER;
  hModifyOrig    NUMBER;
  hTransDDL      NUMBER;

  /* CLOBs to hold DDL */
  /* Orig_ddl0 will hold the baseline DDL for the object to be compared              */
  /* Orig_ddl1 will also hold the baseline DDL for the object to be compared against */
  /*           but will also go through some translations before being compared      */
  /*           against Comp_ddl2.                                                    */
  /* Comp_ddl2 will contain the DDL to be compared against the baseline              */
  Orig_ddl0      CLOB;
  Orig_ddl1      CLOB;
  Comp_ddl2      CLOB;

  ret            NUMBER;
BEGIN
  /* Strip off Attributes not concerned with in DDL                                  */
  /* If you are concerned with TABLESPACE, STORAGE, or SEGMENT information just      */
  /*    comment out these few lines.                                                 */
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',false);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);

/* Loop through each of the rows passed in by the reference cursor                   */
LOOP
  /* Fetch the input cursor into PL/SQL variables                                    */
  FETCH input_values INTO table_name, orig_schema, comp_schema;
  EXIT WHEN input_values%NOTFOUND;

  /* Here is the first use of our handles for pointing to the original table DDL     */
  /* It names the object_type (TABLE),                                               */
  /*    provides the name of the object (our PL/SQL variable table_name), and        */
  /*    states the schema it is from                                                 */
  hOpenOrig0 := DBMS_METADATA.OPEN('TABLE');
  DBMS_METADATA.SET_FILTER(hOpenOrig0,'NAME',table_name);
  DBMS_METADATA.SET_FILTER(hOpenOrig0,'SCHEMA',orig_schema);

  /* Setup handle again for the original table DDL that will undergo transformation */
  /* We setup two handles for the original object DDL because we want to be able to */
  /*    Manipulate one set for comparison but output the original DDL to the user   */
  hOpenOrig := DBMS_METADATA.OPEN('TABLE');
  DBMS_METADATA.SET_FILTER(hOpenOrig,'NAME',table_name);
  DBMS_METADATA.SET_FILTER(hOpenOrig,'SCHEMA',orig_schema);

  /* Setup handle for table to compare original against */
  hOpenComp := DBMS_METADATA.OPEN('TABLE');
  DBMS_METADATA.SET_FILTER(hOpenComp,'NAME',table_name);
  DBMS_METADATA.SET_FILTER(hOpenComp,'SCHEMA',comp_schema);

  /* Modify the transformation of "orig_schema" to take on ownership of "comp_schema" */
  /* If we didn't do this, when we compared the original to the comp objects there    */
  /*    would always be a difference because the schema_owner is in the DDL generated */
  hModifyOrig := DBMS_METADATA.ADD_TRANSFORM(hOpenOrig,'MODIFY');
  DBMS_METADATA.SET_REMAP_PARAM(hModifyOrig,'REMAP_SCHEMA',orig_schema,comp_schema);

  /* This states to created DDL instead of XML to be compared                         */
  hTransDDL := DBMS_METADATA.ADD_TRANSFORM(hOpenOrig0,'DDL');
  hTransDDL := DBMS_METADATA.ADD_TRANSFORM(hOpenOrig ,'DDL');
  hTransDDL := DBMS_METADATA.ADD_TRANSFORM(hOpenComp ,'DDL');

  /* Get the DDD and store into the CLOB PL/SQL variables                             */
  Orig_ddl0 := DBMS_METADATA.FETCH_CLOB(hOpenOrig0);
  Orig_ddl1 := DBMS_METADATA.FETCH_CLOB(hOpenOrig);

  /* Here we are providing for those instances where the baseline object does not     */
  /*     exist in the Comp_schema.                                                    */
  BEGIN
  Comp_ddl2 := DBMS_METADATA.FETCH_CLOB(hOpenComp);
  EXCEPTION
    WHEN OTHERS THEN
        comp_ddl2 := 'DOES NOT EXIST';
  END;

  /* Now simply compare the two DDL statements and output row if not equal            */
  ret  := DBMS_LOB.COMPARE(Orig_ddl1, Comp_ddl2);
  IF ret != 0 THEN
    out_rec.table_name  := table_name;
    out_rec.orig_schema := orig_schema;
    out_rec.orig_ddl    := Orig_ddl0;
    out_rec.comp_schema := comp_schema;
    out_rec.comp_ddl    := Comp_ddl2;
    PIPE ROW(out_rec);
  END IF;

  /* Cleanup and release the handles */
  DBMS_METADATA.CLOSE(hOpenOrig0);
  DBMS_METADATA.CLOSE(hOpenOrig);
  DBMS_METADATA.CLOSE(hOpenComp);
end loop;

RETURN;
END TABLEDDL_FC;
/

To call the function just issue a SELECT statement similar to the following:

SELECT * 
  FROM TABLE(tableddl_fc(CURSOR(SELECT table_name, owner, '<comp_schema>' 
                                  FROM dba_tables where owner = '<orig_schema>')));

The code itself is not that difficult but there is some extreme power in this small function. For my particular use in my development environments I am now able to compare schemas to validate that QA is in sync with production or help assist in determining what objects need to be altered in a move from one environment to the next. While I didn't want to confuse the issue of passing in an object type to compare I hope you can see where we can alter this function very easily for passing in any object type or just letting the function cycle through all valid object types for an owner. Granted, if the schemas are on different databases this procedure will need to be modified slightly to do remote procedure calls.

Comments

I've update your example to support dblink & other types.

check my blog http://agonen.blogspot.com

There is some problem with views compare , I hope to solve it soon.

Hello, I'm having problems with DBMS_METADATA.SET_REMAP_PARAM, it does not exist on 9.2.0.X, not even exist when searching on Metalink.

Thank you.