Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Database diff: WHY: You must own the objects or have select_catalog_role?

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
Answers
-
I have the same problem
Is there a solution?
-
No, I am not aware of a solution. This is still happening for version 21.2.
-
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.
-
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,
-
Added select_catalog role to both users but seem to get same result but just for the Package Body.
-
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.
-
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
-
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 ?>
-
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... */ '?>' ); </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,