Database diff: WHY: You must own the objects or have select_catalog_role?

Dezsoe Kados

When trying to do a database diff following is shown on the diff result screen:

<some package body name> using <some connection name>  connection.

Unable to compare objects.

Ensure that you have the required permissions.

You must own the objects or have select_catalog_role.

The starnge thing is that the  user IS the owner of the compared packages (on both involved connections)

Even tried to grant the "select_catalog_role" to the users but the result is the same.

Btw the package spec comparison of that same package is showing the correct differences.

Any ideas

sqldeveloper 19.4/21.2  (tested on DB versions 12.1 + 19.3)

Message was edited by: Dezsoe Kados

user4484241

I have the same problem

Is there a solution?

Dezsoe Kados

No, I am not aware of a solution. This is still happening for version 21.2.

Mal Baird

Getting the same problem in  Version 21.4.3.063. The connections own the packages and package bodies. Diff shows differences in the package but not the body.

User_2DKLA

The Database Diff feature uses the DBMS_METADATA package to retrieve the metadata of database objects to be compared.

(As shown by the Statements tab in the Statements - Log panel: begin ? := dbms_metadata.get_xml(?, ?, ?); end;)

And it's a documented limitation of DBMS_METADATA that the SELECT_CATALOG_ROLE is required in order to retrieve metadata of database objects in other users' schemas.

If that requirement is not met, there's little SQL Developer can do beside displaying the message shown in @Mal Baird's post.

Regards,

Mal Baird

Added select_catalog role to both users but seem to get same result but just for the Package Body.

User_2DKLA

Well, it works for me (same release of SQL Developer: 21.4.3, Windows 10)...

Did you run SET ROLE ALL in both sessions?

(The SELECT_CATALOG_ROLE must not only be granted, it must be enabled in the session calling DBMS_METADATA.)

Regards,

EDIT: if that counts, tested against DB version 11.2.0.4...

EDIT #2: re-tested against DB version 19.9; again, worked as shown above.

user4381682

This error seems to happen whenever a package contains this sequence:

?>

Database 19c Standard Edition 2 Release 19.0.0.0.0

SQL Developer 21.4.1.349

Mal Baird

user4381682 looks like you are correct.

Package Body includes :-

_xml:='<?xml version="1.0" encoding="utf-16"?>

and other Package Bodies work using connections with or without SELECT_CATALOG_ROLE.

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

SQL Developer Version 21.4.3.063

User_2DKLA can you try ?>

User_2DKLA

Hi,

[Tested on SQL Developer 21.4.3, DB version 19.9.]

@Mal Baird, @user4381682: you're definitely right! The following package body was enough to reproduce the issue:

create or replace package body dbdifftst$owner.pkg_samp as
    procedure foo
    is
    begin
        dbms_output.put_line( /* After the following... */ '?>' );
        dbms_output.put_line( /* ... XML compile will fail
                                 ... on the right bracket char. */ q'[<?]' );
    end foo;
end pkg_samp;
/

I started SQL Developer in debug mode, and upon running the Database Diff tool, the following error was output onto the console:

18:38:33.378 [RaptorTaskThread20-Comparing] ERROR oracle.sysman.dbTarget.db.changemgr.emo.docaccess.CMXMLUtils - 
Caught an exception trying to parse XML document: <Line 20, Column 73>: XML-20201: (Fatal Error) Expected name instead of ].

XML processing happens here because metadata is retrieved by SQL Developer in XML format in the first place, and in the format returned by dbms_metadata.get_xml each line of code is broken down into something like this:

  <SOURCE_LINES_ITEM>
   <OBJ_NUM>24166</OBJ_NUM>
   <LINE>5</LINE>
   <PRE_NAME>0</PRE_NAME>
   <POST_NAME_OFF>0</POST_NAME_OFF>
   <POST_KEYW>0</POST_KEYW>
   <PRE_NAME_LEN>0</PRE_NAME_LEN>
   <POST_ATNAME_OFF>0</POST_ATNAME_OFF>
   <ATNAME_OFF>0</ATNAME_OFF>
   <NEW_VERSION>0</NEW_VERSION>
   <SOURCE>        dbms_output.put_line( /* After the following... */ &apos;?&gt;&apos; );
</SOURCE>
  </SOURCE_LINES_ITEM>

And from these SQL Developer must "reassemble" the text of the package one way or another. But something goes wrong when doing so, and it would seem that the first ?> on line 5 causes some confusion/mismatch which results in the above-mentioned exception, complaining from the presence of the ] character after <? on line 6. Seemingly an exception handler in the Database Diff tool catches that exception, and makes it appear as if missing permissions on the database side had caused dbms_metadata.get_xml not to return the source code—hence the phony error message about the missing grant of the SELECT_CATALOG_ROLE role.

Bottom line: yes, this really looks like a bug in the Database Diff tool. 😕

The following code does not trigger it, but that can hardly be considered a proper workaround.

create or replace package body dbdifftst$owner.pkg_samp as
    procedure foo
    is
    begin
        dbms_output.put_line( /* After the following... */ '?'||'>' );
        dbms_output.put_line( /* ... nothing happens. */ q'[<?]' );
    end foo;
end pkg_samp;
/

Regards,