Hello
I am starting with PL/SQL
i have a use case I have not been able to fix despite thoroughly reading the doc like this:
I have a script that browses all the db_links in a database and fetches data but my script fails/exits when a db_link is down. or unavaialable for whatever reason..
this is the script:
set serveroutput on
whenever sqlerror exit sql.sqlcode;
set echo off
SET VERIFY OFF
set heading off
SET FEEDBACK OFF
set serveroutput on size unlimited
set linesize 300
declare
v_instance VARCHAR2(50);
cursor mylinks is select db_link, owner, created, host, username from all_db_links;
C1 SYS_REFCURSOR;
linkno number := 0;
BEGIN
for linkcur in mylinks loop
open c1 for 'select * from (
select instance_name from v$instance@"'||linkcur.db_link||'") A ';
loop
FETCH C1 INTO v_instance;
EXIT WHEN C1%NOTFOUND;
dbms_output.put_line ( v_instance);
end loop;
close c1;
end loop;
END;
running it as is gives me this kind of errors :
declare
*
ERROR at line 1:
ORA-12541: TNS:no listener
ORA-06512: at line 8
I would like to handle the error and if a database link is down to keep going an dnot exiting because my report is not complete in that case.
i would like to add something like this but I do not know where to put it in my script:
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('ERROR: '||linkcur.db_link )
I have two cursors one that gets all the dblinks on the db form all_dblinks other that connects to each one and gathers the instance_name.. the final purpose is not to fetch the instance name of course but more complex data such as users and roles.
I Hope you can assist me with this..
thanks in advance.