How To Close Database Link In An Distributed Transaction To Avoid Hang (Doc ID 1362546.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 9.2.0.8 and later
Information in this document applies to any platform.
***Checked for relevance on 07-May-2013***


Symptoms

Both of the functions below querying a table from a database link, in the second function the database link is closing.

CREATE OR REPLACE Function testl(P_Passportno Varchar2 )
  Return Number Is
  Dummy Number;
  Lvchk1 Number;
Begin
  Dummy:=0;
  Select Count(*) Into Lvchk1 From Rjtc@Lolclnk
  Where Rjtc_Parssport_No=P_Passportno
  And RJTC_STATUS='REJECT';
  Return(Dummy);
End;
/

CREATE OR REPLACE Function test_with_pragma(P_Passportno Varchar2 )
  Return Number Is
  PRAGMA AUTONOMOUS_TRANSACTION;
  Dummy Number;
  Lvchk1 Number;
Begin
  Dummy:=0;
  Select Count(*) Into Lvchk1 From Rjtc@Lolclnk
  Where Rjtc_Parssport_No=P_Passportno
  And RJTC_STATUS='REJECT';
  rollback;
  DBMS_SESSION.CLOSE_DATABASE_LINK('Lolclnk');
  rollback;
  Return(Dummy);
End;
/

If executed in the following order the session hangs, after the execution of "test_with_pragma" function.

> var a number;
> exec :a:=TESTL('PS122');
PL/SQL procedure successfully completed.

> exec :a:=test_with_pragma('PS122');

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