Home » SQL & PL/SQL » SQL & PL/SQL » Find where exception occurred in oracle procedure (Oracle Plsql, Oracle 12C, Unix/Linux)
Find where exception occurred in oracle procedure [message #667338] Thu, 21 December 2017 10:37 Go to next message
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 #667339 is a reply to message #667338] Thu, 21 December 2017 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

I suspect that a flawed EXCEPTION handler has been implemented.
post EXCERPT of all EXCEPTION handlers involved.
Can you modify the main procedure to start & stop SQL_TRACE?
Re: Find where exception occurred in oracle procedure [message #667341 is a reply to message #667339] Thu, 21 December 2017 11:24 Go to previous messageGo to next message
pavan_mrt
Messages: 3
Registered: December 2017
Junior Member
Hi BlackSwan,

Thank you for your immediate reply.

Since it is a production code, I can't modify the main procedure.
I want to know whether we can trace where the exception is occurring from sqlplus or unix environment.Can you pls help me?
Re: Find where exception occurred in oracle procedure [message #667344 is a reply to message #667341] Thu, 21 December 2017 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
My assessment is that the code has more than 1 bug.
If you can't or won't debug code in place on Production, then you need to reproduce the bug in a test database.

ALTER SESSION SET SQL_TRACE=TRUE; -- is best way to obtain visibility into what occurs inside the code.
I will defer to others to propose other alternatives to determine exact source of ORA-06502 error.
Re: Find where exception occurred in oracle procedure [message #667347 is a reply to message #667344] Thu, 21 December 2017 15:54 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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> 
Re: Find where exception occurred in oracle procedure [message #667348 is a reply to message #667347] Thu, 21 December 2017 17:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
pavan_mrt,

Consider the following.

IMO, if the procedure does any DDL, then I would NOT invoke it manually.
If the procedure does any DML, you run the risk of contaminating the application data.
If the procedure only issues SELECT statement then it should be innocuous to manually invoke it.
If, as I assume, the procedure runs most of the time without any error, then it could prove challenging to discover the correct combination of input parameters that produce the ORA-06502 error or maybe you'll get lucky & the error is easily & quickly reproduced.

Best Wishes & Happy New Year!
Re: Find where exception occurred in oracle procedure [message #667356 is a reply to message #667348] Fri, 22 December 2017 02:50 Go to previous message
pavan_mrt
Messages: 3
Registered: December 2017
Junior Member
Hi Team,

Since we are not having the latest data in the test environment to simulate the issue, I would like to go for option to enable the SQL_TRACE by
taking the help of DBA. Thank you.
Previous Topic: How to cast collection in table
Next Topic: How To Delete older data less than 10 years
Goto Forum:
  


Current Time: Thu Mar 28 16:55:31 CDT 2024