Retrieve Current Executing Point of PL/SQL Call Stack(Oracle Programming)
About pkg_call_stack:
There is a discussion of pl/sql call stack and error stack.
And Tom wrote a article to explain how to retrieve current executing point of pl/sql. But I don't know, why I cannot find the article in asktom site now.
Forturnly, I found a copy from . But there are a bug for using constants. I fixed the bug and pack it to a package. Hope it helpful.
Program:
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() .
See Also
Reference:
A asktom article about call_stack and error_stack.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1529405950004
The original source code of Tom's who_am_i.
http://plsql.wikidot.com/forum:recent-posts