FROMAT_CALL_STACK and DB-link

.. The DB Link Saga continues...

Oracle 8.1.7.0
We are in the process of developing a generic error handling package that will (hopefully!) be able to log errors into an error table using Advanced Queues (to avoid the pragma autonomous_transaction inaccessibility in the distributed transactions). As part of the package, we need to get the call stack. And this is where the issue is:

• These procedures are created on the REMOTE server: CREATE OR REPLACE PROCEDURE TEST_STACK_B AS v_stack VARCHAR2(2000); BEGIN v_stack := 'B: ' || DBMS_UTILITY.FORMAT_CALL_STACK;

  INSERT INTO MYTBL (C1) VALUES (v_stack); END TEST_STACK_B;
/

CREATE OR REPLACE PROCEDURE TEST_STACK_A AS     v_stack VARCHAR2(2000);
BEGIN
  v_stack := 'A: ' || DBMS_UTILITY.FORMAT_CALL_STACK;   INSERT INTO MYTBL (C1) VALUES (v_stack);   

  TEST_STACK_B;    END TEST_STACK_A;
/

Executing the following script on the REMOTE server yields expected results:
BEGIN
    TEST_STACK_A;
    COMMIT;
END;
/

(results from MYTBL )
1 A: ----- PL/SQL Call Stack -----
  object line object
  handle number name

fa5dc408         4  procedure AVILNER.TEST_STACK_A
fa74ebac         2  anonymous block


2  B: ----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
f9e1bbb0         4  procedure AVILNER.TEST_STACK_B
fa5dc408         7  procedure AVILNER.TEST_STACK_A
fa74ebac         2  anonymous block

Running a slightly modified script over the DB Link, produces no call stack AT ALL:

BEGIN
    TEST_STACK_A_at_MY_DB_LINK;
    COMMIT;
END;
/

(results from MYTBL )
1 A:
2 B:

My understanding was that from the remote server's perspective, the invocation of the TEST_STACK_A procedure seems to be almost identical to the regular client connection calling the proc. Why in this case is the call stack not available, and is there ANYTHING (!!!) that can be done to get it?

Thank you in advance, and pardon me for the long post.

--Alex Vilner Received on Fri Sep 27 2002 - 15:00:57 CDT