SQLDeveloper - Version 3.2.10.09
Package bodies (DDL) on both source and destination database are identical but package body on the source database is marked as "invalid" and package body on destination database is marked as "valid" - then Database Diff reports that package bodies different. (but on panel showing DDL for both compared package bodies there is visible no difference).
After compiling package body on the source database (no changes in DDL) and when status of that package body changed to "valid" - now Database Diff correctly shows no difference.
Is that intentional behavior or bug?
If there is a difference in the status of the object, do you want to know about it?
Not in a DIFF report I don't. I expect a DIFF report to show DDL differences in the source code, not the status of a compilation.
In my experience those reports are used to determine whether two different objects are different - not to indicate that the STATUS of the target object is different. There are many reasons why the status may be different and NONE of them can be corrected by altering the DDL of that object itself.
One missing or invalid object (e.g. a missing table) could render any number of other objects (views, functions, procedures, packages) to be invalid. I certainly wouldn't want a DIFF report to report differences in ALL of the views, functions and so on just because one object is missing or invalid. That just generates a lot of useless 'noise' that actually prevents you from seeing if there are any real differences.
For DIFFs if the object itself is the same you want NO output or a simple MATCHED indication; not a lot of false alarms. There are simpler ways to locate invalid objects that visually wading through a DIFF report.
I would consider it a bug if a DIFF report listed differences that are not related to the DDL of the object itself.
According to the CM team, in the previous (pre-MDAPI) incarnation of CM, status was not compared, and got complaints about that. It would be possible to provide the option whether to compare status of objects that have status (PL/SQL objects, triggers, views, indexes) provided MDAPI reports the status, but it's a fairly hefty enhancement.
You can add your voice to SQL Developer feature requests using Feature Requests at the SQL Developer Exchange. This not only makes sure we don't forget, but also gives all users a chance to add their vote for features and provides a forum for you (the user base) to brainstorm on how you prefer it to work.
According to the CM team, in the previous (pre-MDAPI) incarnation of CM, status was not compared, and got complaints about that.
Point taken. Though it seems these days whichever path you take you will get complaints. ;)
It would be possible to provide the option whether to compare status of objects that have status (PL/SQL objects, triggers, views, indexes) provided MDAPI reports the status, but it's a fairly hefty enhancement.
I take that to mean that the results do now, and will continue to, take status into account.
Perhaps my experience is using the functionality differently than what your team is working with.
I make heavy use of the DBMS_METADATA and DBMS_METADATA_DIFF packages. Much of that functionality operates on CLOBS containing data in either the raw XML or the SXML format.
So, to me, an XML in a CLOB has no status. So in comparing XML1 in CLOB1 to XML2 in CLOB2 the status that MIGHT have existed on the real object in the real database is irrelevant. I do comparisons between what is in version control and what is from some other source. That other source might have been, or might still be, an object in a database but status is meaningless since DDL in version control has no status. That is why, for my use cases, I wouldn't want to see a comparison fail due to a status issue.
The DBMS_METADATA_DIFF package contains the interfaces for comparing two metadata documents in SXML format. The result of the comparison is an SXML difference document. This document can be converted to other formats using the DBMS_METADATA submit interface and the CONVERT API.
That is the basis for the comments I was making; that METADATA doesn't necessarily represent an actual object in an actual database.