Query through Dblink within PL/SQL block reports ORA-16000 and PL/SQL: ORA-04053: error occurred when validating remote object ON ADG (Doc ID 2248747.1)

Last updated on APRIL 17, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Query using dblink on ADG withing a PL/SQL block reports

PL/SQL: ORA-04053, ORA-00604, and ORA-16000: when running SIMPLE SELECT WITH PL/SQL:

Call stack :-  kprball  kqdobu kqrcmt ktcCommitTxn_new  ktcCommitTxn

 

Example :-

 

DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY


SQL> conn TEST_USER

 

SQL> REM +=========================================================================
SQL> REM | Run SQL over db link to a Read/Write/Primary database:
SQL> REM +=========================================================================
SQL> select sysdate from dual@TEST_USER_DBLINK;

SYSDATE
---------
16-MAR-17

SQL> REM +=========================================================================
SQL> REM | Notice that the above SQL is successful.
SQL> REM | However, notice what happens when we run the same SQL in a PL/SQL block:
SQL> REM +=========================================================================

 

SQL> declare
2 TEST_DATE date;
3 begin
4 select sysdate into TEST_DATE from dual@TEST_USER_DBLINK;
5 end;
6 /
select sysdate into TEST_DATE from dual@TEST_USER_DBLINK;
*
ERROR at line 4:
ORA-06550: line 4, column 39:
PL/SQL: ORA-04053: error occurred when validating remote object
TEST_USER.DUAL@TEST_USER_DBLINK.WORLDPAYTD.LOCAL
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access
ORA-06550: line 4, column 4:
PL/SQL: SQL Statement ignored

 

 

SQL> REM +=========================================================================
SQL> REM | Setting transaction to read only does not seem to help:
SQL> REM +=========================================================================
SQL> declare
2 TEST_DATE date;
3 begin
4 set transaction read only;
5 select sysdate into TEST_DATE from dual@TEST_USER_DBLINK;
6 end;
7 /
select sysdate into TEST_DATE from dual@TEST_USER_DBLINK;

*
ERROR at line 5:
ORA-06550: line 5, column 39:
PL/SQL: ORA-04053: error occurred when validating remote object
TEST_USER.DUAL@TEST_USER_DBLINK.WORLDPAYTD.LOCAL
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access
ORA-06550: line 5, column 4:
PL/SQL: SQL Statement ignored

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms