Hello everyone,
sorry for the subject.
select * from v$version;
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
"CORE 12.2.0.1.0 Production"
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
Currently I am using dbms_utility and I just wanted to test UTL_CALL_STACK but I got stuck in somewhere. I copied base code from Tim HALL: https://oracle-base.com/articles/12c/utl-call-stack-12cr1
alter session set plsql_optimize_level =1;
create or replace PROCEDURE display_error_stack AS
l_depth PLS_INTEGER;
BEGIN
l_depth := UTL_CALL_STACK.error_depth;
DBMS_OUTPUT.put_line('***** Error Stack Start *****');
DBMS_OUTPUT.put_line('Depth Error Error');
DBMS_OUTPUT.put_line('. Code Message');
DBMS_OUTPUT.put_line('--------- --------- --------------------');
FOR i IN 1 .. l_depth LOOP
DBMS_OUTPUT.put_line(
RPAD(i, 10) ||
RPAD('ORA-' || LPAD(UTL_CALL_STACK.error_number(i), 5, '0'), 10) ||
UTL_CALL_STACK.error_msg(i)
);
END LOOP;
DBMS_OUTPUT.put_line('***** Error Stack End *****');
END;
/
create or replace PACKAGE test_pkg AS
PROCEDURE proc_1;
PROCEDURE proc_2;
PROCEDURE proc_3;
END;
/
create or replace PACKAGE BODY test_pkg AS
PROCEDURE proc_1 AS
BEGIN
proc_2;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('exception in proc_1');
display_error_stack;
END;
PROCEDURE proc_2 AS
BEGIN
proc_3;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('exception in proc_2');
display_error_stack;
RAISE DUP_VAL_ON_INDEX;
END;
PROCEDURE proc_3 AS
BEGIN
RAISE NO_DATA_FOUND;
EXCEPTION
WHEN OTHERS THEN
RAISE TOO_MANY_ROWS;
END;
END;
/
so proc_1 is calling proc_2 and it is calling proc_3. proc_3 raise NO_DATA_FOUND and it is captured in it's own exception block and raise TOO_MANY_ROWS, this error captured in proc_2 and and display error stack and then raise DUP_VAL_ON_INDEX and finally this is captured in proc_1 and display error stack again.
my problem is, display_error_stack in proc_1 shows the first error line as "13" which is proc_3 call but this is not the first error. first error is in proc_3 and line number is "23"! so first error is listed as second in line, why? also display_error_stack in proc_2 shows it correctly, first error in stack is 23 in proc_2 display_error_stack:
exec test_pkg.proc_1;
exception in proc_2
***** Error Stack Start *****
Depth Error Error
. Code Message
--------- --------- --------------------
1 ORA-01422 exact fetch returns more than requested number of rows
2 ORA-06512 at "MUSTAFA.TEST_PKG", line 26
3 ORA-01403 no data found
4 ORA-06512 at "MUSTAFA.TEST_PKG", line 23
***** Error Stack End *****
exception in proc_1
***** Error Stack Start *****
Depth Error Error
. Code Message
--------- --------- --------------------
1 ORA-00001 unique constraint (.) violated
2 ORA-06512 at "MUSTAFA.TEST_PKG", line 18
3 ORA-01422 exact fetch returns more than requested number of rows
4 ORA-06512 at "MUSTAFA.TEST_PKG", line 26
5 ORA-01403 no data found
6 ORA-06512 at "MUSTAFA.TEST_PKG", line 23
7 ORA-06512 at "MUSTAFA.TEST_PKG", line 13
***** Error Stack End *****
why there is a
- 7 ORA-06512 at "MUSTAFA.TEST_PKG", line 13
line in "exception in proc_1"
thanks.