Exceptions in stored procedure using DB link

From: Otakar Hrdina <Otakar.Hrdina_at_poh.cz>
Date: Fri, 6 Aug 1999 11:44:30 +0200
Message-ID: <7oeap4$rmk$1@pumba.class.udg.mx>

Hi Everyone,
I have a problem with communication between 2 Oracle servers. The first Oracle server (ORA) has stored procedure GET_VHDKV which is accessing to another Oracle server (ORA_VHDKV). ORA selects data from 2 tables of server ORA_VHDKV and converts them to one table at the server ORA. Stored procedure uses database link. All works fine except one event: when ORA_VHDKV does not respond and GET_VHDKV is executed, stored procedure GET_VHDKV does not respond until ORA_VHDKV server is not running. Then it generate an error
ORA-02068: following severe error from ORA_VHDKV ORA-03113: end-of-file on communication channel Why stored procedure does not generate an exception when ORA_VHDKV does not respond? Why waits until ORA_VHDKV is responding?
There is a code of stored procedure:

FUNCTION GET_VHDKV(D1 DATE, D2 DATE)
  RETURN INTEGER IS
  FLAG CHAR;
  VALID CHAR;
  ERR INTEGER;
  I_REC VHDKV.AIARCTABLE_at_ORA_VHDKV%ROWTYPE;   REL_REC VHD.REL%ROWTYPE;
  ERRMSG VARCHAR2(250);
  CURSOR C1 IS
    SELECT * FROM VHDKV.AIARCTABLE_at_ORA_VHDKV WHERE RTIME>D1 AND RTIME<D2  UNION
    SELECT * FROM VHDKV.CIARCTABLE_at_ORA_VHDKV WHERE RTIME>D1 AND RTIME<D2;   CURSOR C2(STATNO_ NUMBER, VTYPE_ CHAR, INPUTNO_ NUMBER) IS     SELECT * FROM VHD.REL WHERE VTYPE=VTYPE_ AND ((STATNO=STATNO_ AND INPUTNO=INPUTNO_) OR

                                               (ALTSTATNO=STATNO_ AND

ALTINPUTNO=INPUTNO_));
  BEGIN
    DBMS_OUTPUT.PUT_LINE('SYSDATE=' || SYSDATE || ' D1=' || D1 || ' D2=' || D2);
 --SAVEPOINT SPOINT;
      --when SAVEPOINT SPOINT is active and error ORA-03113 is generated then ROLLBACK TO SPOINTS generates an error

    OPEN C1;
    LOOP

      FETCH C1 INTO I_REC;
      EXIT WHEN C1%NOTFOUND;
      --DBMS_OUTPUT.PUT_LINE(I_REC.IDST || ' ' || I_REC.IDTP || ' ' ||

I_REC.IDLN || ' ' || I_REC.ATIME || ' ' || I_REC.RTIME);    OPEN C2(I_REC.IDST, TO_CHAR(I_REC.IDTP), I_REC.IDLN);

      LOOP
        FETCH C2 INTO REL_REC;
        EXIT WHEN C2%NOTFOUND;

  VALID:=TO_CHAR(SIGN(BITAND(I_REC.PROP, 4)));   IF REL_REC.INPUTNO = I_REC.IDLN THEN
    FLAG:='A';
  ELSIF REL_REC.ALTINPUTNO = I_REC.IDLN THEN     FLAG:='R';
    VALID:='1';
    I_REC.RTIME:=VHD.RC.RND10DATE_(I_REC.RTIME, 0);   END IF;
     --DBMS_OUTPUT.PUT_LINE(REL_REC.ADR1 || ' ' || REL_REC.ADR2 || ' ' || REL_REC.STATNO || '<' || REL_REC.VTYPE || '>' || REL_REC.INPUTNO || ' ' || REL_REC.ALTINPUTNO || ' ' || FLAG);
  BEGIN
     INSERT INTO VHD.ARC VALUES (REL_REC.ADR1, REL_REC.ADR2, I_REC.RTIME, FLAG, VALID, I_REC.RVALUE);
  EXCEPTION
    WHEN OTHERS THEN

      BEGIN
     DBMS_OUTPUT.PUT_LINE('Before ARCEXC: ' || SQLERRM(SQLCODE));
     ERR:=SQLCODE;
         INSERT INTO VHD.ARCEXC VALUES (REL_REC.ADR1, REL_REC.ADR2,

I_REC.RTIME, FLAG, SIGN(BITAND(I_REC.PROP, 4)), I_REC.RVALUE, ERR);    EXCEPTION

     WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('After ARCEXC: ' || SQLERRM(SQLCODE));
        NULL;

   END;
  END;
      END LOOP;
   CLOSE C2;
    END LOOP;
    CLOSE C1;
 COMMIT;
    RETURN 0;
  EXCEPTION

• I need jump here immediatelly when ORA_VHDKV does not respond and terminate this procedure
• with ROLLBACK to SPOINT;
• why exception is not generated and stored procedure is still waiting for ORA_VHDKV ???????? WHEN OTHERS THEN --ROLLBACK TO SPOINT; ERRMSG:=SQLERRM(SQLCODE); DBMS_OUTPUT.PUT_LINE(SQLCODE || ' * ' || ERRMSG); BEGIN INSERT INTO VHD.ORA VALUES (SYSDATE, ERRMSG); EXCEPTION WHEN OTHERS THEN NULL; END; COMMIT; RETURN(-1); END GET_VHDKV;

Thank you very much for any advice.

Robert Reich
reich_at_poh.cz Received on Fri Aug 06 1999 - 04:44:30 CDT