SQL & PL/SQL

DB version: 19.14

I am relatively new to PL/SQL.

In my PL/SQL code (a trigger as shown below), I execute stored procedures in remote DBs via DB Link.
After each execution of the remote procedure, I want to close the DB link.

As demonstrated below under the heading Start of Example1, in normal conditions (not PL/SQL), When I try to close a DB link using "ALTER SESSION CLOSE DATABASE LINK test_db_link02;" command, I get ORA-02080 error as shown below. The solution for this is to issue a "commit;" command and then re-run "ALTER SESSION CLOSE DATABASE LINK test_db_link02;".

I want to adopt the above logic to a trigger code.

In the trigger code show below under My trigger code , I want ORA-02080 error to be handled in the exception block. The exception block should catch ORA-02080 error and issue a commit and then run "ALTER SESSION CLOSE DATABASE LINK <db_LINK_NAME>;".

So, I created a named exception called ex_dblink_in_use as shown below.
For each remote execution (shown with "remote execution line 1,2,..." below) if there is an exception, I want the named execution ex_dblink_in_use to handle it.

Is there a way to dynamically pass the DB link name to place I have mentioned <DB Linkname here> below ?
Othere wise, I will have to add the ex_dblink_in_use exception block for each line (shown with "remote execution line 1,2,..." below). Right ?

All suggestions welcome ; logic, best practices, even naming conventions.

-- Start of Example1

SQL> create database link test_db_link02 connect to depsy identified by tiger#235 using 'TNS_DB02E';

Database link created.

SQL> col global_name for a20

SQL> select * from global_name@test_db_link02;

GLOBAL_NAME
--------------------
CDB_28P.JFX.CH

SQL> ALTER SESSION CLOSE DATABASE LINK test_db_link02;

ERROR:
ORA-02080: database link is in use

SQL> commit;

Commit complete.

SQL> ALTER SESSION CLOSE DATABASE LINK test_db_link02;

Session altered.

--- END OF Example1

--- My trigger code

create or replace trigger trg_abcd
...............
declare
 v_variable1 varchar2(70) ;
 v_variable2 varchar2(64);
 ex_dblink_in_use EXCEPTION;
 PRAGMA EXCEPTION_INIT(ex_dblink_in_use, -2080);
 
begin

--- Some logic ;
   mystored_proc@test_db_link02;  ---> remote execution line 1
   mystored_proc@test_db_link03;  ---> remote execution line 2
   mystored_proc@test_db_link04;  ---> remote execution line 3
   mystored_proc@test_db_link05;  ---> remote execution line 4
   mystored_proc@test_db_link06;  ---> remote execution line 5
   mystored_proc@test_db_link07;
   mystored_proc@test_db_link08;
   mystored_proc@test_db_link09;

 
 

exception
--- just to deal with ORA-02080
WHEN ex_dblink_in_use THEN
	COMMIT;
	EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK <DB Linkname here>';
-- Main exception to Catch silent failures. Like a general safety net
 when others then
   insert into app_maint.bridgesp_error_log (log_time, log_msg) values (systimestamp, 'bla blah: ' || sqlerrm);
   commit;
end;

end;
/