MySQL, Oracle Abc Wiki

CREATE OR REPLACE PACKAGE pkg_call_stack is
-- history:
--   create            tom
--   modify 2010.05.08 f.yang  fix string parse bug
 
    PROCEDURE  who_called_me (
        owner      OUT   VARCHAR2,
        name       OUT   VARCHAR2,
        lineno     OUT   NUMBER,
        caller_t   OUT   VARCHAR2
     );
    FUNCTION STUDY2.who_am_i
        RETURN VARCHAR2;
END;
/
 
CREATE OR REPLACE PACKAGE BODY STUDY2.pkg_call_stack is
-- history:
--   create            tom
--   modify 2010.05.08 f.yang  fix string parse bug
 
FUNCTION who_am_i
    RETURN VARCHAR2 IS
    l_owner                       VARCHAR2 (30);
    l_name                        VARCHAR2 (30);
    l_lineno                      NUMBER;
    l_type                        VARCHAR2 (30);
BEGIN
    pkg_call_stack.who_called_me (l_owner, l_name, l_lineno, l_type);
    RETURN l_type || ' line(' || l_lineno || ') ' || l_owner || '.' || l_name;
END;
 
PROCEDURE who_called_me (
    owner      OUT   VARCHAR2,
    name       OUT   VARCHAR2,
    lineno     OUT   NUMBER,
    caller_t   OUT   VARCHAR2
 ) AS
    call_stack                    VARCHAR2 (4096) DEFAULT DBMS_UTILITY.format_call_stack;
    n                             NUMBER;
    found_stack                   BOOLEAN DEFAULT FALSE;
    line                          VARCHAR2 (255);
    cnt                           NUMBER := 0;
    fixed_call_stack              VARCHAR2 (4096) := '';
 BEGIN
    -- 2010/05/07 f.yang modify    comment out dbms_output of the original program
    -- DBMS_OUTPUT.put_line (call_stack);
 
    LOOP
       n             := INSTR (call_stack, CHR (10));
       EXIT WHEN (   cnt = 3
                  OR n IS NULL
                  OR n = 0);
       line          := SUBSTR (call_stack, 1, n - 1);
       call_stack    := SUBSTR (call_stack, n + 1);
 
       IF (NOT found_stack) THEN
          IF (line LIKE '%handle%number%name%') THEN
             found_stack    := TRUE;
          END IF;
       ELSE
          cnt    := cnt + 1;
 
          -- cnt = 1 is ME
          -- cnt = 2 is MY Caller
          -- cnt = 3 is Their Caller
          IF (cnt = 3) THEN
             -- 2010/05/07 f.yang modify    use regexp replace the constant
             -- lineno    := TO_NUMBER (TRIM (SUBSTR (line, 13, 6)));
             lineno := regexp_substr(line, ' +\d+ +');
             -- 2010/05/07 f.yang modify    use regexp replace the constant
             -- line      := SUBSTR (line, 21);
             line := regexp_replace(line, '^([[:alnum:]]+)( +\d+ +)', '');
 
             IF (line LIKE 'pr%') THEN
                n    := LENGTH ('procedure ');
             ELSIF (line LIKE 'fun%') THEN
                n    := LENGTH ('function ');
             ELSIF (line LIKE 'package body%') THEN
                n    := LENGTH ('package body ');
             ELSIF (line LIKE 'pack%') THEN
                n    := LENGTH ('package ');
             ELSIF (line LIKE 'anonymous%') THEN
                n    := LENGTH ('anonymous block ');
             ELSE
                n    := NULL;
             END IF;
 
             IF (n IS NOT NULL) THEN
                caller_t    := LTRIM (RTRIM (UPPER (SUBSTR (line, 1, n - 1))));
             ELSE
                caller_t    := 'TRIGGER';
             END IF;
 
             line      := SUBSTR (line, NVL (n, 1));
             n         := INSTR (line, '.');
             owner     := LTRIM (RTRIM (SUBSTR (line, 1, n - 1)));
             NAME      := LTRIM (RTRIM (SUBSTR (line, n + 1)));
          END IF;
       END IF;
    END LOOP;
END;
 
END;
/
 
Sample:
 
CREATE OR REPLACE procedure STUDY2.cs_sample is
begin
    dbms_output.put_line(pkg_call_stack.who_am_i);
end;
/
 
Result:
 
SQL> exec cc_smaple;
PROCEDURE line(3) STUDY2.CS_SMAPLE
 
SQL> exec dbms_output.put_line(pkg_call_stack.who_am_i);
ANONYMOUS BLOCK line(1) .
 
SQL> select pkg_call_stack.who_am_i from dual;
 
WHO_AM_I
------------------------------------------------
 
 line() .