1 2 Previous Next 19 Replies Latest reply: Aug 1, 2013 6:29 PM by rp0428 Go to original post RSS
      • 15. Re: how can I compare 2 views?
        Greg Spall

        oooo, good find. That's probably the "least DDL involved" solution yet.

        • 16. Re: how can I compare 2 views?
          rp0428

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

          What is 'wrong' is that you did not check the results of each step before you did the next one. The doc explains each of those steps.

           

          Steps 6 & 7 show you the differences in the views.

           

          As the doc example at step 6 said

           

           

          The SXML difference document shows the union of the two SXML documents, with the XML attributes value1 and src identifying the differences. When an element exists in only one document it is marked with src. Thus, <COL_LIST_ITEM src="1"> means that this element is in the first document (TAB1) but not in the second. When an element is present in both documents but with different values, the element's value is the value in the second document and the value1 gives its value in the first. For example, <LENGTH value1="10">20</LENGTH> means that the length is 10 in TAB1 (the first document) and 20 in TAB2.

          Your own post for those steps shows that the NAME and SUBQUERY elements are different. If you read the doc text above you will see how to tell the differences.

           

          Then your own post for step 9 shows the XML that has those two items in the ALTER_LIST element

           

             <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>

           

          Then your own post for step 10 shows the list of ALTER statements created

           

             <ALTER_LIST></ALTER_LIST>

          Gee - an empty list. That means that ALTER VIEW can NOT be used to modify those two items.

           

          Let's check the SQL Language doc and confirm that

          http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_4004.htm

           

          You cannot use this statement to change the definition of an existing view. Further, if DDL changes to the view's base tables invalidate the view, then you cannot use this statement to compile the invalid view. In these cases, you must redefine the view using CREATE VIEW with the OR REPLACE keywords.

          And there is your ANSWER!

           

          For views you can use DBMS_METADATA_DIFF to determine that two views are different and to determine what those differences are. But, as the doc says, you CANNOT use ALTER VIEW to 'change the definition of an existing view'.

           

          You must redefine the view.

           

          And you still haven't answered my questions about whether you think the view examples I provided are identical or not. As applied to views the term 'identical' may have a different meaning to Oracle than it does to you.

          • 17. Re: how can I compare 2 views?
            Erhan_toronto

            I think these two is not identical.. so what the oracle thinks?

             

            Thanks for your explanation for oracle API document. I didn't know that there is no alter statement for views, only create/ replace... One quick question after getting the differences at step 7:

             

            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

             

            how can I have the result like different or not? so I can use get_ddl to create view or any other object type.

            • 18. Re: how can I compare 2 views?
              SomeoneElse

              > I didn't know that there is no alter statement for views, only create/ replace.

               

              Are you kidding me?  I've been telling you this for two days:

               

              What is the correct way to compare view, synonym, Trigger, and Type using DBMS_METADATA_DIFF ?

               

              And there IS an alter statement for views, it just won't do what you want.

               

              Man oh man.

              • 19. Re: how can I compare 2 views?
                rp0428

                I think these two is not identical.. so what the oracle thinks?

                Don't be afraid of breaking Oracle by actually trying things. You learn by DOING - not by having someone give you an answer.

                1 2 Previous Next