Hello Everybody.
DB Version : 12. 1.0.2
I am trying to find the line number of an exception raised deep inside a package.
Here is the set up similar to the production package (sort of )...
code_1:
CREATE OR REPLACE PACKAGE a_pkg_test AS
PROCEDURE main_proc(p_string IN VARCHAR2 DEFAULT NULL);
END;
CREATE OR REPLACE PACKAGE BODY a_pkg_test AS
PROCEDURE sub_proc IS
BEGIN
/* variable Assignments and DMLs and Anonymous Blocks
*/
BEGIN
INSERT INTO dept ( SELECT 98, 'deptname_98', 'deptloc' FROM DUAL);
INSERT INTO dept ( SELECT 100,'deptname_100', 'deptloc' FROM DUAL ); -- ERROR line# 11
INSERT INTO dept ( SELECT 99, 'deptname_99', 'deptloc' FROM DUAL);
/* OTHER DML Statement
*/
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/* variable Assignments and DMLs and a few Anonymous Blocks
*/
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
PROCEDURE main_proc(p_string IN VARCHAR2 DEFAULT NULL) IS
BEGIN
sub_proc;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR:' || SQLCODE||','|| SQLERRM); /* goes into logging tables */
RAISE;
END;
END;
Now when run ...
SQL> exec a_pkg_test.main_proc;
ERROR:-1438,ORA-01438: value larger than specified precision allowed for this
column
BEGIN a_pkg_test.main_proc; END;
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at "USER.A_PKG_TEST", line 31
ORA-06512: at line 1
I added a call to DBMS_Utility.Format_Call_Stack at line# 20 in the above code (inside the anonymous block hosting the error stmt)
Even then i am not getting the correct line number ( expecting line# 11)
SQL> exec a_pkg_test.main_proc;
----- PL/SQL Call Stack -----
object line object
handle number
name
700010089639218 17 package body USER.A_PKG_TEST
700010089639218 28 package body USER.A_PKG_TEST
7000100893726f0 1 anonymous block
ERROR:-1438,ORA-01438: value larger than specified precision allowed for this
column
BEGIN a_pkg_test.main_proc; END;
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
ORA-06512: at "USER.A_PKG_TEST", line 32
ORA-06512: at line 1
Questions :
1. How do i find out the exact line number the exception occurred in ?
2. How do i find out the this same info if i did not have access to modify code . ( Just call it from anonymous blocks ) ?
Thanks for you time and effort !!.