How to get the line number where an exception occurred in the stored procedure

I am working with a large stored procedure. Currently I have this exception code below to catch any exceptions which occur. What I really need information on, is what line number the exception occurred on.  Is there a way to get the line number where the error occurred?



EXCEPTION
 WHEN OTHERS THEN
  errMsg := "Error occurred ' || SUBSTR(SQLERRM, 1, 199);
  dbms_output.put_line(errMsg);
  RETURN;
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
That is the problem with having a generic "exception" section at the end of a large procedure: Oracle does not tell you then which line in the procedure the error actually happened on.  Your options are:
1. Remove or comment out the generic "exception" section at the end
2. Add a varchar2(2) variable in the declare section of the procedure, named something like: v_step; add lines like this in various places in the procedure: "v_step := '1';",  "v_step := '2';" etc;  add this command in your "exception" section (and make sure that you have "serveroutput on") : dbms_output_put_line('After step: '||v_step);
3. Add nested "begin ...exception ...end" sections around each query or SQL statement in the procedure, so the error message in each local exception block can be very specific.
1
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I don't know of a way to get the line number directly but take a look at DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.

There is a good paper on it here:
http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.