Permalink
Find file
145716d Dec 30, 2015
159 lines (116 sloc) 4.76 KB
-- ---------------------------------------------------------------------------------------------------
--
-- Script: stk.sql
--
-- Author: Adrian Billington
-- www.oracle-developer.net
--
-- Description: A package for parsing the PL/SQL call stack. Includes functions to identify a
-- program's name or its caller.
--
-- Usage: Use in PL/SQL. The available functions are as follows:
--
-- a) WHOAMI returns either SCHEMA.OBJECT or anonymous block;
--
-- b) CALLER returns either SCHEMA.OBJECT or anonymous block of a program's
-- invoker. Additionally, the program name of any level in the stack can
-- be optionally returned;
--
-- c) PARSE is exposed to return an entire line of the stack at the supplied
-- depth.
--
-- ---------------------------------------------------------------------------------------------------
CREATE PACKAGE stk AS
FUNCTION parse (
depth_in IN PLS_INTEGER DEFAULT 2
) RETURN VARCHAR2;
FUNCTION caller (
depth_in IN PLS_INTEGER DEFAULT 3
) RETURN VARCHAR2;
FUNCTION whoami RETURN VARCHAR2;
END stk;
/
CREATE PACKAGE BODY stk AS
TYPE ntt_varchar2 IS TABLE OF VARCHAR2(1028);
FUNCTION string_to_table (
string_in IN VARCHAR2,
delimiter_in IN VARCHAR2 DEFAULT ','
) RETURN ntt_varchar2 IS
v_wkg_str VARCHAR2(32767) := string_in || delimiter_in;
v_pos PLS_INTEGER;
nt_return ntt_varchar2 := ntt_varchar2();
BEGIN
LOOP
v_pos := INSTR(v_wkg_str,delimiter_in);
EXIT WHEN NVL(v_pos,0) = 0;
nt_return.EXTEND;
nt_return(nt_return.LAST) := TRIM(SUBSTR(v_wkg_str,1,v_pos-1));
v_wkg_str := SUBSTR(v_wkg_str,v_pos+1);
END LOOP;
RETURN nt_return;
END string_to_table;
--------------------------------------------------------------
FUNCTION parse (
depth_in IN PLS_INTEGER DEFAULT 2
) RETURN VARCHAR2 IS
v_call_stack VARCHAR2(4096);
nt_stack_lines ntt_varchar2;
c_recsep CONSTANT VARCHAR2(1) := CHR(10);
c_headlines CONSTANT PLS_INTEGER := 3;
BEGIN
/* Get the call stack, removing the trailing newline... */
v_call_stack := RTRIM(DBMS_UTILITY.FORMAT_CALL_STACK, c_recsep);
/* Turn the call stack into a collection of lines... */
nt_stack_lines := string_to_table(v_call_stack, c_recsep);
/* Return the depth required (ignoring the header lines)... */
RETURN nt_stack_lines(depth_in + c_headlines);
EXCEPTION
WHEN SUBSCRIPT_BEYOND_COUNT THEN
RETURN NULL;
END parse;
--------------------------------------------------------------
FUNCTION caller (
depth_in IN PLS_INTEGER DEFAULT 3
) RETURN VARCHAR2 IS
v_callinfo VARCHAR2(255);
v_proginfo VARCHAR2(128);
v_lineinfo PLS_INTEGER;
v_return VARCHAR2(128);
c_colsep CONSTANT VARCHAR2(2) := ' '; -- two spaces
c_no_stack CONSTANT VARCHAR2(32) := 'Caller information not available';
BEGIN
/* Get the call information from the call stack... */
v_callinfo := stk.parse(depth_in);
/* Strip out the program and line number... */
IF v_callinfo IS NOT NULL THEN
v_proginfo := TRIM(SUBSTR(v_callinfo, INSTR(v_callinfo, c_colsep, -1) + LENGTH(c_colsep)));
v_lineinfo := TO_NUMBER(TRIM(SUBSTR(v_callinfo, INSTR(v_callinfo, c_colsep),
INSTR(v_callinfo, c_colsep || v_proginfo) -
INSTR(v_callinfo, c_colsep))));
v_return := v_proginfo ||', line '|| NVL(TO_CHAR(v_lineinfo), '<none>');
ELSE
v_return := c_no_stack;
END IF;
RETURN v_return;
END caller;
--------------------------------------------------------------
FUNCTION whoami RETURN VARCHAR2 AS
v_callinfo VARCHAR2(255);
v_program VARCHAR2(128);
c_anonymous CONSTANT VARCHAR2(15) := 'anonymous block';
c_colsep CONSTANT VARCHAR2(2) := ' '; -- one space
BEGIN
/* Get the call information from the call stack... */
v_callinfo := stk.parse(3);
/* If an anonymous block, then return as such, else derive the program name... */
IF INSTR(v_callinfo, c_anonymous) > 0 THEN
v_program := '<' || c_anonymous || '>';
ELSE
v_program := TRIM(SUBSTR(v_callinfo, INSTR(v_callinfo, c_colsep, -1) + LENGTH(c_colsep)));
END IF;
RETURN v_program;
END whoami;
END stk;
/
CREATE OR REPLACE PUBLIC SYNONYM stk FOR stk;
GRANT EXECUTE ON stk TO PUBLIC;