Hi Tom

I am trying to create a function that can compare objects in a local and a remote schema, using DBMS_METADATA_DIFF.
For this, I am using
https://docs.oracle.com/database/121/SUTIL/GUID-E9A778A7-2E9D-4240-8707-A52FF65556FE.htm#SUTIL3627
as an inspiration. However, the example only uses tables in the local schema, and I have found that referencing other schemas in the same database is an issue. 

connect DBAUSER/pwd@DB1

create or replace   function get_object_sxml
  ( object_type   in varchar2
  , object_name   in varchar2
  , schema_name   in varchar2 default null
  , network_link  in varchar2 default null
  ) 
    return clob 
--  authid current_user
  is
    --
    open_handle number;
    transform_handle number;
    doc clob;
  begin
    open_handle := dbms_metadata.open( object_type => object_type, network_link => network_link );
    if ( schema_name is not null )
    then
      dbms_metadata.set_filter(open_handle,'SCHEMA', schema_name);
    end if;
    dbms_metadata.set_filter(open_handle,'NAME', object_name );
    --
    -- Use the 'SXML' transform to convert XML to SXML
    transform_handle := dbms_metadata.add_transform(open_handle,'SXML');
    --
    -- Use this transform parameter to suppress physical properties
    dbms_metadata.set_transform_param(transform_handle,'PHYSICAL_PROPERTIES', false);
    doc := dbms_metadata.fetch_clob(open_handle);
    dbms_metadata.close(open_handle);
    return doc;
end;
/

create user scott identified by sdfjasdkfjh;
alter user scott quota 1m on users;
create table scott.mydual as select * from sys.dual;
select get_object_sxml('TABLE','MYDUAL','SCOTT',null) from dual;

ORA-31603: object "MYDUAL" of type TABLE not found in schema "SCOTT"
ORA-06512: at "SYS.DBMS_METADATA", line 7709
ORA-06512: at "SYS.DBMS_METADATA", line 7673
ORA-06512: at "DBADMIN.GET_OBJECT_SXML", line 26
ORA-06512: at line 1

This issue is described in MOS note 463483.1, which gives two solutions.
1) Using AUTHID CURRENT_USER + granting SELECT ANY DICTIONARY to the user.
2) Creating the procedure in SYS.

I would really not like to use SYS. One reason being, that it is generally disallowed for security reasons, but also, since it means added work and risks when migrating to tx. Exadata using Datapump (as objects in SYS are not transferred).

Option 1) means, that every user who wants to use the procedure should create the database links needed in their private schema.
Is there any way around this, I would really like to use a common schema for the procedure and database links.

Let's create the same procedure, but with AUTHID:

create or replace   function get_object_sxml
  ( object_type   in varchar2
  , object_name   in varchar2
  , schema_name   in varchar2 default null
  , network_link  in varchar2 default null
  ) 
    return clob 
authid current_user
  is
....



( as a side note) - querying remotely works fine:

connect system/manager@DB2

create user scott identified by sdfjasdkfjh;
alter user scott quota 1m on users;
create table scott.mydual as select * from sys.dual;
alter table scott.mydual add ( id number default 0 not null  );


connect DBAUSER/pwd@DB1

create database link db2 connect to XYZ using PWD using 'DB2';

select get_object_sxml('TABLE','MYDUAL','SCOTT','DB2') from dual;

=> Works fine.



But, the issue is here - querying from another user

grant execute on get_object_sxml to public;


connect scott/tiger@DB1

select dbauser.get_object_sxml('TABLE','MYDUAL','SCOTT','DB2') from dual;

ORA-02019: connection description for remote database not found
ORA-06512: at "SYS.DBMS_METADATA", line 5652
ORA-06512: at "SYS.DBMS_METADATA", line 5845
ORA-06512: at "DBADMIN.GET_OBJECT_SXML", line 15
ORA-06512: at line 1

So, when the procedure is specifed with AUTHID CURRENT_USER, we are not using the database link in the schema that the procedure is created in.

create database link db2 connect to XYZ using PWD using 'DB2';

select dbadmin.get_object_sxml('TABLE','MYDUAL','SCOTT','DB2') from dual;

=> Success


However, requiring that every user creates the needed database links is not very elegant or usable.

How can I do this, without requiring every user to create a number of database links ?


Br,
Kim