sa_stack_trace( )

Each record in the result set represents a single call on the stack. If the compound statement is not part of a procedure, function, trigger, or event, the type of batch (watcom_batch or tsql_batch) is returned instead of the procedure name.

This procedure returns the same information as the STACK_TRACE function.

None

None.

• TRY statement
• BEGIN statement
• ERROR_LINE function [Miscellaneous]
• ERROR_MESSAGE function [Miscellaneous]
• ERROR_PROCEDURE function [function type]
• ERROR_SQLCODE function [Miscellaneous]
• ERROR_SQLSTATE function [Miscellaneous]
• ERROR_STACK_TRACE function [Miscellaneous]
• STACK_TRACE function [Miscellaneous]
• sa_error_stack_trace system procedure
• Nested compound statements and exception handlers

This example shows how to obtain the result set columns from the sa_stack_trace system procedure:

SELECT StackLevel, UserName, ProcName, LineNumber FROM sa_stack_trace();

When this statement is executed outside of the context of a stored procedure, the result set is empty.

The following example shows the implementation of a general stack trace procedure that sends its results to the client window. An example of its use is included.

CREATE OR REPLACE PROCEDURE StackDump( MSG CHAR(128) )
BEGIN
    DECLARE myStackLevel UNSIGNED SMALLINT;
    DECLARE myUserName CHAR(128);
    DECLARE myProcName CHAR(128);
    DECLARE myLineNumber UNSIGNED SMALLINT;
    DECLARE err_notfound
        EXCEPTION FOR SQLSTATE '02000';
    DECLARE myStack CURSOR FOR 
        SELECT StackLevel, UserName, ProcName, LineNumber FROM sa_stack_trace();
    MESSAGE 'Stack Trace: ' || MSG TO CLIENT;
    OPEN myStack;
    StackLoop: LOOP
        FETCH NEXT myStack
            INTO myStackLevel, myUserName, myProcName, myLineNumber;
        IF SQLSTATE = err_notfound THEN
            LEAVE StackLoop;
        END IF;
        IF myStackLevel != 1 THEN
            MESSAGE myStackLevel - 1 || ' ' || myUserName || ' ' || myProcName || ' ' || myLineNumber
                TO CLIENT;
        ENDIF
    END LOOP StackLoop; 
    CLOSE myStack;
END;

CREATE OR REPLACE PROCEDURE Proc1()
BEGIN
    CALL Proc2();
END;

CREATE OR REPLACE PROCEDURE Proc2()
BEGIN
    CALL Proc3();
END;

CREATE OR REPLACE PROCEDURE Proc3()
BEGIN
    CALL StackDump('Snapshot from Proc3');
END;

CALL Proc1();

The Interactive SQL Messages window output is shown below.

Stack Trace: Snapshot from proc3
1 DBA proc3 3
2 DBA proc2 3
3 DBA proc1 3