This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Aug 1, 2013 4:29 PM by rp0428 RSS

how can I compare 2 views?

Erhan_toronto Newbie
Currently Being Moderated

I am using below to compare 2 indexes, but is there any way to compare two views in oracle to see the result like identical, not Exists or different by structure perspective ?

 

 

select decode

       (

            sum(decode(column_name_1, column_name_2, 0, 1))

          , 0

          , 'Identical'

          , null

          , 'Not exist'

          , 'Different'

       ) index_status

  from (

          select column_position

               , max(decode(index_owner, 'USER1' , column_name)) column_name_1

               , max(decode(index_owner, 'USER2', column_name)) column_name_2

            from all_ind_columns

           where table_name = 'TBL_A'

           group

              by column_position

       )

  • 1. Re: how can I compare 2 views?
    Ishan Journeyer
    Currently Being Moderated

    Looks like you are on a roll. So many threads about objects comparison?

     

    What exactly are you looking for?

  • 2. Re: how can I compare 2 views?
    Erhan_toronto Newbie
    Currently Being Moderated

    Thanks ishan for your response,

     

    I have user1 and user2 and these two users has same named view for both of them. want to be sure the structure is same

     

    user1 has VIEW_TBL_A

    user2 has VIEW_TBL_A

     

    I like to compare these two views if they are identical or not?

  • 3. Re: how can I compare 2 views?
    Mike Kutz Expert
    Currently Being Moderated

    SQL Developer has a schema comparison tool.

  • 4. Re: how can I compare 2 views?
    Erhan_toronto Newbie
    Currently Being Moderated

    Is there any way to do it manually?

  • 5. Re: how can I compare 2 views?
    Greg.Spall Expert
    Currently Being Moderated

    well, following the style of this:

    Best of 2006: Web 0.1 - The Daily WTF

     

    You could take a photo of the two views using your smart phone.

    Print them out, set them beside each other and eye ball them

     

    Seriously, though, you're best bet would be to compare the "text" field of the dba_views tables, however, them being "LONG" data type makes that tricky.

     

    I've looked around for an "easy" way to convert LONG to varchar or clob, but all options seem to be pl/sql and create tables. *yuck*.

    Somebody really should create a little function that does this

  • 7. Re: how can I compare 2 views?
    rp0428 Guru
    Currently Being Moderated

    Erhan_toronto wrote:

     

    I am using below to compare 2 indexes, but is there any way to compare two views in oracle to see the result like identical, not Exists or different by structure perspective ?

     

    Why would you do it manually? Just use Oracle's DBMS_METADA_DIFF package.

    http://docs.oracle.com/cd/E11882_01/server.112/e22490/metadata_api.htm#BABIDHGE

     

    Using the DBMS_METADATA_DIFF API to Compare Object Metadata

    This section provides an example that uses the retrieval, comparison, and submit interfaces of DBMS_METADATA and DBMS_METADATA_DIFF to fetch metadata for two tables, compare the metadata, and generate ALTER statements which make one table like the other. For simplicity, function variants are used throughout the example.

    The example is for tables but just use views instead.

  • 8. Re: how can I compare 2 views?
    Erhan_toronto Newbie
    Currently Being Moderated

    I did used DBMS_METADA_DIFF for comparing 2 views but no luck didnt work. Which means need to do manually . Any help?

  • 9. Re: how can I compare 2 views?
    rp0428 Guru
    Currently Being Moderated

    Erhan_toronto wrote:

     

    I did used DBMS_METADA_DIFF for comparing 2 views but no luck didnt work. Which means need to do manually . Any help?

    Then you didn't do it right. I modified the example in the doc I provided to use views instead of tables and it worked just fine.

  • 10. Re: how can I compare 2 views?
    Erhan_toronto Newbie
    Currently Being Moderated

    Thanks rp,

     

    I will try again... but can you send me the result that you get?

  • 11. Re: how can I compare 2 views?
    rp0428 Guru
    Currently Being Moderated

    You're confused about how the forums work.

     

    It is YOU that posts the results that you get and tell us how you got them and why they aren't the results you expected.

     

    Despite all of the threads you have created you still do not provide your 4 digit Oracle version or any indication that you even tried to modify that doc code to work with views.

     

    You also need to be aware of what it even means for two views to be identical.

     

    Do you consider these two views to be identical?

     

    CREATE VIEW V1 AS SELECT EMPNO, ENAME FROM TABLE1; -- view owned by user 1
    CREATE VIEW V2 AS SELECT EMPNO, ENAME FROM TABLE1; -- view owned by user 2

    If you think those two views are identical then what if I told you that these are the table definitions

    CREATE TABLE USER1.TABLE1 (EMPNO RAW, ENAME FLOAT);
    CREATE TABLE USER2.TABLE1 (EMPNO NUMBER, ENAME VARCHAR2(30));

    Now do you think the views are identical? Does Oracle think those views are identical?

  • 12. Re: how can I compare 2 views?
    Erhan_toronto Newbie
    Currently Being Moderated

    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    PL/SQL Release 11.2.0.1.0 - Production

    CORE 11.2.0.1.0 Production

    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

    NLSRTL Version 11.2.0.1.0 - Production

    ---------------------------------------------------------------------------------------------------------------------

     

     

      CREATE TABLE TBL_A

       ( "FIELD_A1" NUMBER NOT NULL ENABLE,

      "FIELD_A2" VARCHAR2(50),

      "FIELD_A4" NUMBER(5,2) DEFAULT 0,

      "FIELD_A5" VARCHAR2(10) NOT NULL ENABLE

       );

     

    insert into TBL_A values (111,'Jane',55,'fff');

     

    CREATE OR REPLACE VIEW VIEW_TBL_A as

      SELECT FIELD_A1

      FROM TBL_A

      WHERE FIELD_A1 = 111;

     

     

      CREATE TABLE TBL_B"

       ( "FIELD_A1" NUMBER NOT NULL ENABLE,

      "FIELD_A2" VARCHAR2(20),

      "FIELD_A4" NUMBER(5,2) NOT NULL ENABLE,

      "FIELD_A5" VARCHAR2(10),

      "FIELD_A6" NUMBER(2,0) NOT NULL ENABLE

       ) ;

     

    insert into TBL_B values (110,'Jane',50,'fff',12)

     

     

    CREATE OR REPLACE VIEW VIEW_TBL_B as

      SELECT FIELD_A1

      FROM TBL_B

      WHERE FIELD_A1 = 110;

     

    After going to link     Using the Metadata APIs    and   modifying / following the steps, I used below query at step 14... but nothing happened

     

    SELECT dbms_metadata_diff.compare_alter('VIEW','VIEW_TBL_A','VIEW_TBL_B') FROM dual;

     

    result:

     

    dbms_metadata_diff.compare_alter('VIEW','VIEW_TBL_A','VIEW_TBL_B')

    ----------------------------------------------------------------------------------------------------------

    no result here

  • 13. Re: how can I compare 2 views?
    rp0428 Guru
    Currently Being Moderated

     

    After going to link     Using the Metadata APIs    and   modifying / following the steps, I used below query at step 14... but nothing happened

     

    If you don't want help don't post threads in the forum.

     

    That example does NOT start at step 14 and it does NOT use the tables you just posted.

     

    There are NO SHORTCUTS. You have to do every step, do them in order and look at the result of each step before you do the next stepl.

     

    You also didn't answer any of the questions that I ask.

     

    Feel free to wait for others to respond.

  • 14. Re: how can I compare 2 views?
    Erhan_toronto Newbie
    Currently Being Moderated

    I didn't started at step 14... I did followings....What is wrong on my steps?

     

    Step 1: Create two tables, and two views

     

     

    CREATE TABLE TBL_A

       ( "FIELD_A1" NUMBER NOT NULL ENABLE,

      "FIELD_A2" VARCHAR2(50),

      "FIELD_A4" NUMBER(5,2) DEFAULT 0,

      "FIELD_A5" VARCHAR2(10) NOT NULL ENABLE

       );

     

    insert into TBL_A values (111,'Jane',55,'fff');

     

    CREATE OR REPLACE VIEW VIEW_TBL_A as

      SELECT FIELD_A1

      FROM TBL_A

      WHERE FIELD_A1 = 111;

     

     

      CREATE TABLE TBL_B"

       ( "FIELD_A1" NUMBER NOT NULL ENABLE,

      "FIELD_A2" VARCHAR2(20),

      "FIELD_A4" NUMBER(5,2) NOT NULL ENABLE,

      "FIELD_A5" VARCHAR2(10),

      "FIELD_A6" NUMBER(2,0) NOT NULL ENABLE

       ) ;

     

    insert into TBL_B values (110,'Jane',50,'fff',12)

     

     

    CREATE OR REPLACE VIEW VIEW_TBL_B as

      SELECT FIELD_A1

      FROM TBL_B

      WHERE FIELD_A1 = 110;

     

     

    Step 2: Create a function to return the view metadata in SXML format. The following are some key points to keep in mind about SXML when you are using theDBMS_METADATA_DIFF API:

     

    CREATE OR REPLACE FUNCTION get_view_sxml(name IN VARCHAR2) RETURN CLOB IS

    open_handle NUMBER;

    transform_handle NUMBER;

    doc CLOB;

    BEGIN

    open_handle := DBMS_METADATA.OPEN('VIEW');

    DBMS_METADATA.SET_FILTER(open_handle,'NAME',name);

    transform_handle := DBMS_METADATA.ADD_TRANSFORM(open_handle,'SXML');

    DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle,'PHYSICAL_PROPERTIES',

    FALSE);

    doc := DBMS_METADATA.FETCH_CLOB(open_handle);

    DBMS_METADATA.CLOSE(open_handle);

    RETURN doc;

    END;

     

    Step 3: Use the get_views_sxml function to fetch the table SXML for the two Views:

     


    result:  SELECT get_view_sxml('VIEW_TBL_A') FROM dual;


     

     

      <VIEW xmlns="http://xmlns.oracle.com/ku" version="1.0">

       <SCHEMA>USER1</SCHEMA>

       <NAME>VIEW_TBL_A</NAME>

       <COL_LIST>

          <COL_LIST_ITEM>

             <NAME>FIELD_A1</NAME>

          </COL_LIST_ITEM>

       </COL_LIST>

       <SUBQUERY>SELECT FIELD_A1

      FROM TBL_A

      WHERE FIELD_A1 = 111</SUBQUERY>

    </VIEW>

     

     

    result: SELECT get_view_sxml('VIEW_TBL_B') FROM dual;

     

     

      <VIEW xmlns="http://xmlns.oracle.com/ku" version="1.0">

       <SCHEMA>USER1</SCHEMA>

       <NAME>VIEW_TBL_B</NAME>

       <COL_LIST>

          <COL_LIST_ITEM>

             <NAME>FIELD_A1</NAME>

          </COL_LIST_ITEM>

       </COL_LIST>

       <SUBQUERY>SELECT FIELD_A1

      FROM TBL_B

      WHERE FIELD_A1 = 110</SUBQUERY>

    </VIEW>

     

    4.Compare the results using the DBMS_METADATA browsing APIs:

    SELECT dbms_metadata.get_sxml('VIEW','VIEW_TBL_A') FROM dual;

    SELECT dbms_metadata.get_sxml('VIEW','VIEW_TBL_B') FROM dual;



    Step 5: Create a function using the DBMS_METADATA_DIFF API to compare the metadata for the two Views. In this function, the get_view_sxml function that was just defined in step 2 is used.

     

     

    CREATE OR REPLACE FUNCTION compare_view_sxml(name1 IN VARCHAR2, name2 IN VARCHAR2) RETURN CLOB IS

    doc1 CLOB;

    doc2 CLOB;

    diffdoc CLOB;

    openc_handle NUMBER;

    BEGIN

    doc1 := get_view_sxml(name1);

    doc2 := get_view_sxml(name2);

    openc_handle := DBMS_METADATA_DIFF.OPENC('VIEW');

    DBMS_METADATA_DIFF.ADD_DOCUMENT(openc_handle,doc1);

    DBMS_METADATA_DIFF.ADD_DOCUMENT(openc_handle,doc2);

    diffdoc := DBMS_METADATA_DIFF.FETCH_CLOB(openc_handle);

    DBMS_METADATA_DIFF.CLOSE(openc_handle);

    RETURN diffdoc;

    END;

     

    Step 6: Use the function to fetch the SXML difference document for the two Views:

     

    SELECT compare_view_sxml('VIEW_TBL_A','VIEW_TBL_B') FROM dual;

     

    <VIEW xmlns="http://xmlns.oracle.com/ku" version="1.0">

      <SCHEMA>USER1</SCHEMA>

      <NAME value1="VIEW_TBL_A">VIEW_TBL_B</NAME>

      <COL_LIST>

        <COL_LIST_ITEM>

          <NAME>FIELD_A1</NAME>

        </COL_LIST_ITEM>

      </COL_LIST>

      <SUBQUERY value1="SELECT FIELD_A1

      FROM TBL_A

      WHERE FIELD_A1 = 111">SELECT FIELD_A1

      FROM TBL_B

      WHERE FIELD_A1 = 110</SUBQUERY>

    </VIEW>

     

    Step 7 : Compare the result using the DBMS_METADATA_DIFF browsing APIs:

     

    SELECT dbms_metadata_diff.compare_sxml('VIEW','VIEW_TBL_A','VIEW_TBL_B') FROM dual;

     

    <VIEW xmlns="http://xmlns.oracle.com/ku" version="1.0">

      <SCHEMA>USER1</SCHEMA>

      <NAME value1="VIEW_TBL_A">VIEW_TBL_B</NAME>

      <COL_LIST>

        <COL_LIST_ITEM>

          <NAME>FIELD_A1</NAME>

        </COL_LIST_ITEM>

      </COL_LIST>

      <SUBQUERY value1="SELECT FIELD_A1

      FROM TBL_A

      WHERE FIELD_A1 = 111">SELECT FIELD_A1

      FROM TBL_B

      WHERE FIELD_A1 = 110</SUBQUERY>

    </VIEW>

     

    Step 8: Create a function using the DBMS_METADATA.CONVERT API to generate an ALTERXML document. This is an XML document containing ALTER statements to make one object like another. You can also use parse items to get information about the individual ALTER statements. (This example uses the functions defined thus far.)


     

     

    CREATE OR REPLACE FUNCTION get_view_alterxml(name1 IN VARCHAR2, name2 IN VARCHAR2) RETURN CLOB IS

       diffdoc CLOB;

       openw_handle NUMBER;

      transform_handle NUMBER;

      alterxml CLOB;

      BEGIN

    diffdoc := compare_view_sxml(name1,name2);

    openw_handle := DBMS_METADATA.OPENW('VIEW');

    transform_handle := DBMS_METADATA.ADD_TRANSFORM(openw_handle,'ALTERXML');

      DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'CLAUSE_TYPE');

    DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'NAME');

    DBMS_METADATA.SET_PARSE_ITEM(openw_handle,'COLUMN_ATTRIBUTE');

    DBMS_LOB.CREATETEMPORARY(alterxml, TRUE );

    DBMS_METADATA.CONVERT(openw_handle,diffdoc,alterxml);

    DBMS_METADATA.CLOSE(openw_handle);

    RETURN alterxml;

    END;

     

     

    Step 9:Use the function to fetch the ALTER_XML document:

     

    SELECT get_view_alterxml('VIEW_TBL_A','VIEW_TBL_B') FROM dual;

     

     

     

    <ALTER_XML xmlns="http://xmlns.oracle.com/ku" version="1.0">

       <OBJECT_TYPE>VIEW</OBJECT_TYPE>

       <OBJECT1>

          <SCHEMA>USER1</SCHEMA>

          <NAME>VIEW_TBL_A</NAME>

       </OBJECT1>

       <OBJECT2>

          <SCHEMA>USER1</SCHEMA>

          <NAME>VIEW_TBL_B</NAME>

       </OBJECT2>

       <ALTER_LIST>

          <ALTER_LIST_ITEM>

             <PARSE_LIST>

                <PARSE_LIST_ITEM>

                   <ITEM>NAME</ITEM>

                   <VALUE>VIEW_TBL_B</VALUE>

                </PARSE_LIST_ITEM>

             </PARSE_LIST>

          </ALTER_LIST_ITEM>

          <ALTER_LIST_ITEM>

             <PARSE_LIST>

                <PARSE_LIST_ITEM>

                   <ITEM>CLAUSE_TYPE</ITEM>

                   <VALUE>SUBQUERY</VALUE>

                </PARSE_LIST_ITEM>

             </PARSE_LIST>

          </ALTER_LIST_ITEM>

       </ALTER_LIST>

    </ALTER_XML>

     

     

    Step 10: Compare the result using the DBMS_METADATA_DIFF browsing API:

     

    SELECT dbms_metadata_diff.compare_alter_xml('VIEW','VIEW_TBL_A','VIEW_TBL_A') FROM dual;

     

     

    <ALTER_XML xmlns="http://xmlns.oracle.com/ku" version="1.0">

       <OBJECT_TYPE>VIEW</OBJECT_TYPE>

       <OBJECT1>

          <SCHEMA>USER1</SCHEMA>

          <NAME>VIEW_TBL_A</NAME>

       </OBJECT1>

       <OBJECT2>

          <SCHEMA>USER1</SCHEMA>

          <NAME>VIEW_TBL_A</NAME>

       </OBJECT2>

       <ALTER_LIST></ALTER_LIST>

    </ALTER_XML>

     

    Step 11: The ALTER_XML document contains an ALTER_LIST of each of the alters. Each ALTER_LIST_ITEM has a PARSE_LIST containing the parse items as name-value pairs and a SQL_LIST containing the SQL for the particular alter. You can parse this document and decide which of the SQL statements to execute, using the information in the PARSE_LIST. (Note, for example, that in this case one of the alters is a DROP_COLUMN, and you might choose not to execute that.)

     

     

    Step 12: Create one last function that uses the DBMS_METADATA.CONVERT API and the ALTER DDL transform to convert the ALTER_XML document into SQL DDL:


    CREATE OR REPLACE FUNCTION get_view_alterddl(name1 IN VARCHAR2, name2 IN VARCHAR2) RETURN CLOB IS

        alterxml CLOB;

       openw_handle NUMBER;

        transform_handle NUMBER;

        alterddl CLOB;

       BEGIN

    alterxml := get_view_alterxml(name1,name2);

    openw_handle := DBMS_METADATA.OPENW('TABLE');

    transform_handle := DBMS_METADATA.ADD_TRANSFORM(openw_handle,'ALTERDDL');

    DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle,'SQLTERMINATOR',true);

    DBMS_LOB.CREATETEMPORARY(alterddl, TRUE );

    DBMS_METADATA.CONVERT(openw_handle,alterxml,alterddl);

    DBMS_METADATA.CLOSE(openw_handle);

      RETURN alterddl;

    END;

     

    Step 13:Use the function to fetch the SQL ALTER statements:

    SELECT get_view_alterddl('VIEW_TBL_A','VIEW_TBL_B') FROM dual;

     

     

    get_view_alterddl('VIEW_TBL_A','VIEW_TBL_B')

    ----------------------------------------------------------------------

    no result

     

    Step 14: Compare the results using the DBMS_METADATA_DIFF browsing API:


    SELECT dbms_metadata_diff.compare_alter('VIEW','VIEW_TBL_A','VIEW_TBL_B') FROM dual;


    dbms_metadata_diff.compare_alter('VIEW','VIEW_TBL_A','VIEW_TBL_B')

    -------------------------------------------------------------------------------------

    no result

1 2 Previous Next