Find where exception occurred in oracle procedure [message #667338] |
Thu, 21 December 2017 10:37  |
 |
pavan_mrt
Messages: 3 Registered: December 2017
|
Junior Member |
|
|
Hi,
I am new member in Oracle FAQ's.
We are using Java as front-end and Oracle as back-end.And I am working on support system.And we are using Oracle 12C.
Recently I got a ticket saying the user is facing an error and not able to proceed further.
When checked internally, the java program is calling a procedure in-turn it is throwing an exception ORA-06502 ( numeric or value error). When I dig into the package, The main procedure is internally calling few more procedures where the exceptions were not handled.
I have the package name and the main procedure with parameters.There is so much of logic and calling other procedures with in that main procedure.
But I don't have the log files to check exactly where the code has been broken.In this case I want to check how I need to trace the exception where the code has been broken. Can anyone please help me.
|
|
|
|
|
|
Re: Find where exception occurred in oracle procedure [message #667347 is a reply to message #667344] |
Thu, 21 December 2017 15:54   |
 |
Littlefoot
Messages: 21782 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
If you can, connect to the database using SQL*Plus (or SQL Developer, TOAD or any other tool you use). As you know procedure name and parameters it gets, run it as
begin
package_name.procedure_name(parameter_1, parameter_2, ...);
end;
/
It will, probably, fail, but Oracle might tell you exact error place (procedure name & line). For example:
SQL> create or replace procedure p_test (par_id in number) is
2 l_cnt number;
3 begin
4 select count(*)
5 into l_cnt
6 from employees
7 where department_id = par_id;
8
9 l_cnt := l_cnt / 0; --> will raise an error
10 end;
11 /
Procedure created.
SQL>
SQL> begin
2 p_test (90);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "HR.P_TEST", line 9 --> see? It really is line 9 in procedure P_TEST
ORA-06512: at line 2
SQL>
|
|
|
|
|