Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: ORA-04053: error occurred when validating remote object (oracle12c)
PL/SQL: ORA-04053: error occurred when validating remote object [message #662767] Thu, 11 May 2017 13:45 Go to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
I have shell script that calls PLSQL script. The job connect to standby database
and capture some info and update that captured info in another remote database(READ WRITE).

Here is the plsql script, which is calling from shell script.

set serveroutput on
declare
v_instance varchar2(30);
v_starttime date;
v_instance_status varchar2(30);
v_db_status  varchar2(30);
v_instance_role  varchar2(30);
v_open_mode  varchar2(30);
v_protection_mode varchar2(30);
v_database_role varchar2(30);
v_switchover_status varchar2(30);
v_dataguard_broker varchar2(30);
v_host_name varchar2(100);
v_db_name varchar2(50);
v_force_logging varchar2(10);
begin
select
a.instance_name,
a.startup_time,
a.status ,
a.database_status,
a.instance_role,
a.host_name,
b.name,
b.open_mode,
b.protection_mode,
b.database_role,
b.switchover_status,
b.dataguard_broker,
b.force_logging
into
v_instance,
v_starttime,
v_instance_status ,
v_db_status,
v_instance_role,
v_host_name,
v_db_name,
v_open_mode,
v_protection_mode,
v_database_role,
v_switchover_status,
v_dataguard_broker,
v_force_logging
from
v$instance a,
v$database b;
update dbstatus@crdblink
set startup_time = v_starttime,
instance_status = v_instance_status,
database_status = v_db_status,
instance_role = v_instance_role,
open_mode = v_open_mode,
protection_mode = v_protection_mode,
database_role=v_database_role,
switchover_status = v_switchover_status,
dataguard_broker = v_dataguard_broker,
force_logging=v_force_logging,
last_update_dt = sysdate
where
instance_name=v_instance
and dbname=v_db_name
and hostname=v_host_name;
commit;
end;
/
alter session close database link CRDBLINK;
exit

Here is the shell script which calls the above PLSQL code

#!/usr/bin/ksh
#set -x

. /home/oracle/scripts/conf/set_env.sh

filelist=$(cat $CONF/all_instance.txt | grep -v "#"|sort)
for i in $filelist
do
$ORACLE_HOME/bin/sqlplus -S $DB_CREDENTIAL@$i  @$SQL_FILE/dbstatusupdate.sql
done

The shells script connect to one of the standby database
and capture DB info and update on table which is existing on other READ WRITE database.

I am getting below error when i run the shell.

SQL> @dbstatusupdate.sql.orig
update dbstatus@crdblink
       *
ERROR at line 47:
ORA-06550: line 47, column 8:
PL/SQL: ORA-04053: error occurred when validating remote object
DBMON.DBSTATUS@CRDBLINK
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database or pluggable database open for read-only access
ORA-06550: line 47, column 1:
PL/SQL: SQL Statement ignored


ERROR:
ORA-02080: database link is in use


But every thing works great when i convert the PLSQL code to stored procedure.

Any one please share any thought why it works with stored procedure. But
does not work for plsql code?


Thank you!

Re: PL/SQL: ORA-04053: error occurred when validating remote object [message #662768 is a reply to message #662767] Thu, 11 May 2017 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-16000: database or pluggable database open for read-only access
 *Cause:  The database or pluggable database was opened for read-only access.
          Attempts to modify the database using DML or DDL statements
          generate this error.
 *Action: In order to modify the database or pluggable database, it must
          first be shut down and reopened for read/write access.
Quote:
But every thing works great when i convert the PLSQL code to stored procedure.
Prove it and show us the actual execution.
And you didn't say if this a local or remote procedure.

[Updated on: Thu, 11 May 2017 15:11]

Report message to a moderator

Re: PL/SQL: ORA-04053: error occurred when validating remote object [message #670735 is a reply to message #662768] Mon, 23 July 2018 09:40 Go to previous messageGo to next message
shrinika
Messages: 306
Registered: April 2008
Senior Member
It is local procedure. Thanks!
Re: PL/SQL: ORA-04053: error occurred when validating remote object [message #670736 is a reply to message #670735] Mon, 23 July 2018 11:10 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Pfff! Thanks, more than one year I held my breath.

Previous Topic: Oracle materialized views workflow
Next Topic: passing date value to the query from sql*plus
Goto Forum:
  


Current Time: Thu Mar 28 14:58:13 CDT 2024