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
srcidentifying the differences. When an element exists in only one document it is marked with
<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
value1gives 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
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
Then your own post for step 10 shows the list of ALTER statements created
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
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
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.
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
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.
> 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:
And there IS an alter statement for views, it just won't do what you want.
Man oh man.