This discussion is archived
8 Replies Latest reply: Nov 6, 2012 3:02 AM by dz_r RSS

Database Diff - Identical package bodies are reported as different

dz_r Newbie
Currently Being Moderated
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?
  • 1. Re: Database Diff - Identical package bodies are reported as different
    908002 Expert
    Currently Being Moderated
    Seems intentional. PAckage status might be dependent on other objects of the database..

    Even the source code is same, the dependent object on the source database makes the package invalid.. and after compiling its valid.

    Ex, if you alter any table /view/ underlying structure of dependent obect, then the pacakge will become invalid and need recompilation.
  • 2. Re: Database Diff - Identical package bodies are reported as different
    dz_r Newbie
    Currently Being Moderated
    If this is intentional - are there chances to change that behavior?

    I think it is more practical when "Diff" shows only differences in DLL, not in status of objects.
  • 3. Re: Database Diff - Identical package bodies are reported as different
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated
    If there is a difference in the status of the object, do you want to know about it?

    If I'm comparing 2 environments and some of the pl/sql objects are invalid in A and not in B, I probably want to know that in a DIFF report.

    However, we should probably be marking the differences as such.
  • 4. Re: Database Diff - Identical package bodies are reported as different
    rp0428 Guru
    Currently Being Moderated
    >
    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.
  • 5. Re: Database Diff - Identical package bodies are reported as different
    JoyceScapicchio Journeyer
    Currently Being Moderated
    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.

    Joyce Scapicchio
    SQL Developer Team
  • 6. Re: Database Diff - Identical package bodies are reported as different
    rp0428 Guru
    Currently Being Moderated
    >
    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.

    Oracle® Database PL/SQL Packages and Types Reference
    http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_metadiff.htm
    >
    85 DBMS_METADATA_DIFF

    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.

    Thanks for the links; I will add my vote there.
  • 7. Re: Database Diff - Identical package bodies are reported as different
    972750 Newbie
    Currently Being Moderated
    I have been using DIFF for a few days and also noted DROP statements are not created for those objects found only on the target database. That would be a plus.
  • 8. Re: Database Diff - Identical package bodies are reported as different
    dz_r Newbie
    Currently Being Moderated
    In both cases I'd like to have a choice:
    - Generate or not generate drop statements
    - Compare or not compare status of packages/package bodies

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points