Error ORA-00942 running DBMS_COMPARISON.CONVERGE subprogram

Question

I get the ORA-00942 - table or view does not exist - error when I run a stored procedure which use the DBMS_COMPARISON package to sync two tables over two different schemas.

This is roughly what I have:

remote table        local comparison     local table    
DB1.PRODUCTS        DB2.PRODUCTS_CMP     DB2.PRODUCTS

This is the local DB2 procedure:

create or replace PROCEDURE SYNC_SINGLE AS
          L_SCAN_INFO         SYS.DBMS_COMPARISON.COMPARISON_TYPE;
          L_RESULT            BOOLEAN;   
          L_COMPNAME          VARCHAR2(30);
BEGIN
          L_COMPNAME:='PRODUCT_CMP';
          
          SYS.DBMS_COMPARISON.PURGE_COMPARISON(
                   COMPARISON_NAME => L_COMPNAME
          );

          L_RESULT := 
                    SYS.DBMS_COMPARISON.COMPARE (
                    COMPARISON_NAME => L_COMPNAME,
                    SCAN_INFO       => L_SCAN_INFO,
                    PERFORM_ROW_DIF => TRUE
          );

          FOR ROW_ID IN (
                    SELECT    SCAN_ID
                    FROM      USER_COMPARISON_SCAN_SUMMARY
                    WHERE     COMPARISON_NAME = L_COMPNAME
                              AND STATUS = 'BUCKET DIF'
          )
          LOOP      
                    SYS.DBMS_COMPARISON.CONVERGE (
                              COMPARISON_NAME  => L_COMPNAME,
                              SCAN_ID          => ROW_ID.SCAN_ID,
                              SCAN_INFO        => L_SCAN_INFO,
                              CONVERGE_OPTIONS => SYS.DBMS_COMPARISON.CMP_CONVERGE_REMOTE_WINS, 
                              PERFORM_COMMIT   => FALSE
                    );
          END LOOP ROW_ID;

          SYS.DBMS_COMPARISON.PURGE_COMPARISON(
                   COMPARISON_NAME => L_COMPNAME
          );

END SYNC_SINGLE;

The procedure ends with these errors:

ORA-00942: table or view does not exist
ORA-06512: a "SYS.DBMS_COMPARISON", line 734
ORA-06512: a "SYS.DBMS_COMPARISON", line 5964
ORA-06512: a "SYS.DBMS_COMPARISON", line 682
ORA-06512: a "DB2.SYNC_SINGLE", line 26
ORA-06512: a "DB2.SYNC_SINGLE", line 26
ORA-06512: a line 2

I guess there is a permission issue here but I can't figure out what it is. The user SYS have got all the permissions granted on the local table DB2.PRODUCTS. Also, I have other tables on the same schemas that I sync in the same way and for those tables everything goes just fine. Looking at the tables SYS.COMPARISON$ and SYS.COMPARISON_COL$ everything seems to be configured correctly.

Can anyone help suggesting what I should check out?

Answer 1

I found what it was: AUTHID CURRENT_USER was missing in the procedure declaration.

create or replace PROCEDURE SYNC_SINGLE AUTHID CURRENT_USER AS