SQL owa_util.get_procedure and $$PLSQL_UNIT
Build a generic PL/SQL exception handler using `owa_util.get_procedure` to easily identify the source of an error in your Oracle code.
In 10g and 11g I use the “owa_util.get_procedure” function. I normally use this in packages as it will also return the name of an internal procedure or function as part of the package name, i.e. (package_name).(procedure name). I use this to provide a generic EXCEPTION template for identifying where an exception occurred.
CREATE OR REPLACE procedure some_procedure is
v_procedure_name varchar2(32);
begin
v_procedure_name := owa_util.get_procedure;
end;
CREATE OR REPLACE PACKAGE some_package
AS
FUNCTION v_function_name
RETURN DATE;
END;
/
CREATE OR REPLACE PACKAGE BODY some_package
AS
FUNCTION v_function_name
RETURN DATE
IS
BEGIN
RETURN SYSDATE;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR IN '||owa_util.get_procedure);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
END;
/
Source: https://stackoverflow.com/questions/286549/find-out-name-of-pl-sql-procedure#286569
alter session set plscope_settings='identifiers:all'
/
Session altered.
SQL> set serverout on
SQL> create or replace package pkg
as
procedure p1;
procedure p2;
end pkg;
/
Package created.
SQL> create or replace package body pkg
as
procedure get_scope (obj varchar2, line int)
as
begin
for c in (select rpad (lpad (' ', 2 * (level - 1)) || name, 20, '.') || ' ' || rpad (type, 20) || rpad (usage, 20) identifier_usage_contexts
from user_identifiers t
where level != 1
start with line = get_scope.line and object_name = obj
connect by usage_id = prior usage_context_id)
loop
dbms_output.put_line (c.identifier_usage_contexts);
end loop;
end get_scope;
procedure p1
as
begin
null;
end p1;
procedure p2
as
begin
get_scope ($$plsql_unit, $$plsql_line);
end p2;
end pkg;
Return information about witch module call this code.
CREATE OR REPLACE FUNCTION MIBP_OWNER.FN_WHO_AM_I (p_lvl NUMBER DEFAULT 0)
RETURN VARCHAR2 IS
/***********************************************************************************************
FN_WHO_AM_I returns the full ORACLE name of your object including schema and package names
--
FN_WHO_AM_I(0) - returns the name of your object
FN_WHO_AM_I(1) - returns the name of calling object
FN_WHO_AM_I(2) - returns the name of object, who called calling object
etc., etc., etc.... Up to to he highest level
-------------------------------------------------------------------------------------------------
Copyrigth GARBUYA 2010
*************************************************************************************************/
TYPE str_varr_t IS VARRAY (2) OF CHAR (1);
TYPE str_table_t IS TABLE OF VARCHAR2 (256);
TYPE num_table_t IS TABLE OF NUMBER;
v_stack VARCHAR2 (2048) DEFAULT UPPER (DBMS_UTILITY.format_call_stack);
v_tmp_1 VARCHAR2 (1024);
v_tmp_2 VARCHAR2 (1024);
v_pkg_name VARCHAR2 (32);
v_obj_type VARCHAR2 (32);
v_owner VARCHAR2 (32);
v_idx NUMBER := 0;
v_pos1 NUMBER := 0;
v_pos2 NUMBER := 0;
v_line_nbr NUMBER := 0;
v_blk_cnt NUMBER := 0;
v_str_len NUMBER := 0;
v_bgn_cnt NUMBER := 0;
v_end_cnt NUMBER := 0;
it_is_comment BOOLEAN := FALSE;
it_is_literal BOOLEAN := FALSE;
v_literal_arr str_varr_t := str_varr_t ('''', '"');
v_blk_bgn_tbl str_table_t
:= str_table_t (' IF '
,' LOOP '
,' CASE '
,' BEGIN ');
v_tbl str_table_t := str_table_t ();
v_blk_bgn_len_tbl num_table_t := num_table_t ();
BEGIN
v_stack := SUBSTR (v_stack, INSTR (v_stack, CHR (10), INSTR (v_stack, 'FN_WHO_AM_I')) + 1) || 'ORACLE'; -- skip myself
FOR v_pos2 IN 1 .. p_lvl LOOP -- advance to the input level
v_pos1 := INSTR (v_stack, CHR (10));
v_stack := SUBSTR (v_stack, INSTR (v_stack, CHR (10)) + 1);
END LOOP;
v_pos1 := INSTR (v_stack, CHR (10));
IF v_pos1 = 0 THEN
RETURN (v_stack);
END IF;
v_stack := SUBSTR (v_stack, 1, v_pos1 - 1); -- get only current level
v_stack := TRIM (SUBSTR (v_stack, INSTR (v_stack, ' '))); -- cut object handle
v_line_nbr := TO_NUMBER (SUBSTR (v_stack, 1, INSTR (v_stack, ' ') - 1)); -- get line number
v_stack := TRIM (SUBSTR (v_stack, INSTR (v_stack, ' '))); -- cut line number
v_pos1 := INSTR (v_stack, ' BODY');
IF v_pos1 = 0 THEN
RETURN (v_stack);
END IF;
v_pos1 := INSTR (v_stack, ' ', v_pos1 + 2); -- find end of object type
v_obj_type := SUBSTR (v_stack, 1, v_pos1 - 1); -- get object type
v_stack := TRIM (SUBSTR (v_stack, v_pos1 + 1)); -- get package name
v_pos1 := INSTR (v_stack, '.');
v_owner := SUBSTR (v_stack, 1, v_pos1 - 1); -- get owner
v_pkg_name := SUBSTR (v_stack, v_pos1 + 1); -- get package name
v_blk_cnt := 0;
it_is_literal := FALSE;
--
FOR v_idx IN v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST LOOP
v_blk_bgn_len_tbl.EXTEND (1);
v_blk_bgn_len_tbl (v_blk_bgn_len_tbl.LAST) := LENGTH (v_blk_bgn_tbl (v_idx));
END LOOP;
--
FOR src IN (
SELECT ' ' || REPLACE (TRANSLATE (UPPER (text), ';(' || CHR (10), ' '), '''''', ' ') || ' ' text
FROM all_source
WHERE owner = v_owner
AND name = v_pkg_name
AND TYPE = v_obj_type
AND line < v_line_nbr
ORDER BY line
) LOOP
v_stack := src.text;
IF it_is_comment THEN
v_pos1 := INSTR (v_stack, '*/');
IF v_pos1 > 0 THEN
v_stack := SUBSTR (v_stack, v_pos1 + 2);
it_is_comment := FALSE;
ELSE
v_stack := ' ';
END IF;
END IF;
--
IF v_stack != ' ' THEN
--
v_pos1 := INSTR (v_stack, '/*');
WHILE v_pos1 > 0 LOOP
v_tmp_1 := SUBSTR (v_stack, 1, v_pos1 - 1);
v_pos2 := INSTR (v_stack, '*/');
IF v_pos2 > 0 THEN
v_tmp_2 := SUBSTR (v_stack, v_pos2 + 2);
v_stack := v_tmp_1 || v_tmp_2;
ELSE
v_stack := v_tmp_1;
it_is_comment := TRUE;
END IF;
v_pos1 := INSTR (v_stack, '/*');
END LOOP;
--
IF v_stack != ' ' THEN
v_pos1 := INSTR (v_stack, '--');
IF v_pos1 > 0 THEN
v_stack := SUBSTR (v_stack, 1, v_pos1 - 1);
END IF;
--
IF v_stack != ' ' THEN
FOR v_idx IN v_literal_arr.FIRST .. v_literal_arr.LAST LOOP
v_pos1 := INSTR (v_stack, v_literal_arr (v_idx));
WHILE v_pos1 > 0 LOOP
v_pos2 := INSTR (v_stack, v_literal_arr (v_idx), v_pos1 + 1);
IF v_pos2 > 0 THEN
v_tmp_1 := SUBSTR (v_stack, 1, v_pos1 - 1);
v_tmp_2 := SUBSTR (v_stack, v_pos2 + 1);
v_stack := v_tmp_1 || v_tmp_2;
ELSE
IF it_is_literal THEN
v_stack := SUBSTR (v_stack, v_pos1 + 1);
it_is_literal := FALSE;
ELSE
v_stack := SUBSTR (v_stack, 1, v_pos1 - 1);
it_is_literal := TRUE;
END IF;
END IF;
v_pos1 := INSTR (v_stack, v_literal_arr (v_idx));
END LOOP;
END LOOP;
--
IF v_stack != ' ' THEN
WHILE INSTR (v_stack, ' ') > 0 LOOP
v_stack := REPLACE (v_stack, ' ', ' ');
END LOOP;
v_stack := REPLACE (v_stack, ' END IF ', ' END ');
v_stack := REPLACE (v_stack, ' END LOOP ', ' END ');
--
IF v_stack != ' ' THEN
v_stack := ' ' || v_stack;
v_pos1 := INSTR (v_stack, ' FUNCTION ') + INSTR (v_stack, ' PROCEDURE ');
IF v_pos1 > 0 THEN
v_obj_type := TRIM (SUBSTR (v_stack, v_pos1 + 1, 9)); -- get object type
v_stack := TRIM (SUBSTR (v_stack, v_pos1 + 10)) || ' '; -- cut object type
v_stack := SUBSTR (v_stack, 1, INSTR (v_stack, ' ') - 1); -- get object name
v_tbl.EXTEND (1);
v_tbl (v_tbl.LAST) := v_obj_type || ' ' || v_owner || '.' || v_pkg_name || '.' || v_stack;
END IF;
--
v_pos1 := 0;
v_pos2 := 0;
v_tmp_1 := v_stack;
v_tmp_2 := v_stack;
FOR v_idx IN v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST LOOP
v_str_len := NVL (LENGTH (v_tmp_1), 0);
v_tmp_1 := REPLACE (v_tmp_1, v_blk_bgn_tbl (v_idx), NULL);
v_bgn_cnt := NVL (LENGTH (v_tmp_1), 0);
v_pos1 := v_pos1 + (v_str_len - v_bgn_cnt) / v_blk_bgn_len_tbl (v_idx);
v_str_len := NVL (LENGTH (v_tmp_2), 0);
v_tmp_2 := REPLACE (v_tmp_2, ' END ', NULL);
v_end_cnt := NVL (LENGTH (v_tmp_2), 0);
v_pos2 := v_pos2 + (v_str_len - v_end_cnt) / 5; --- 5 is the length(' END ')
END LOOP;
IF v_pos1 > v_pos2 THEN
v_blk_cnt := v_blk_cnt + 1;
ELSIF v_pos1 < v_pos2 THEN
v_blk_cnt := v_blk_cnt - 1;
IF v_blk_cnt = 0
AND v_tbl.COUNT > 0 THEN
v_tbl.DELETE (v_tbl.LAST);
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
END LOOP;
RETURN CASE v_tbl.COUNT
WHEN 0 THEN
'UNKNOWN'
ELSE
v_tbl (v_tbl.LAST)
END;
END;
/
Show errors;
Other good example use new features Oracle 10g
DECLARE
local_exception EXCEPTION;
FUNCTION nested_local_function
RETURN BOOLEAN IS
retval BOOLEAN := FALSE;
BEGIN
RAISE local_exception;
RETURN retval;
END;
BEGIN
IF nested_local_function THEN
dbms_output.put_line('No raised exception');
END IF;
EXCEPTION
WHEN others THEN
dbms_output.put_line('DBMS_UTILITY.FORMAT_CALL_STACK');
dbms_output.put_line('------------------------------');
dbms_output.put_line(dbms_utility.format_call_stack);
dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_BACKTRACE');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_STACK');
dbms_output.put_line('-------------------------------');
dbms_output.put_line(dbms_utility.format_error_stack);
END;
/
This script produces the following output:
DBMS_UTILITY.FORMAT_CALL_STACK
------------------------------
----- PL/SQL Call Stack -----
object line object
handle number
name
20909240 18 anonymous block
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
-----------------------------------
ORA-06512: at line 7
ORA-06512: at line 11
DBMS_UTILITY.FORMAT_ERROR_STACK
-------------------------------
ORA-06510: PL/SQL: unhandled user-defined exception
Karol PreiskornDecember 10th, 2017plsql 0 comments on SQL owa_util.get_procedure and $$PLSQL_UNIT1585oracle, plsql
Subscribe
Connect with
I allow to create an account
When you login first time using a Social Login button, we collect your account public profile information shared by Social Login provider, based on your privacy settings. We also get your email address to automatically create an account for you in our website. Once your account is created, you'll be logged-in to this account.
DisagreeAgree
Connect with
I allow to create an account
When you login first time using a Social Login button, we collect your account public profile information shared by Social Login provider, based on your privacy settings. We also get your email address to automatically create an account for you in our website. Once your account is created, you'll be logged-in to this account.
DisagreeAgree
0 Comments
Newest
Oldest
Most Voted
Inline Feedbacks
View all comments
