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?
I found what it was: AUTHID CURRENT_USER was missing in the procedure declaration.
create or replace PROCEDURE SYNC_SINGLE AUTHID CURRENT_USER AS