How to find the actual source line number mentioned in ORA error

Hi - I’m trying to determine how to find the true line number of the error being thrown in a PL/SQL package. Our QA group is testing in one of their environments so I’m not able to compile and step through the code to see exactly where it’s failing, and I know the line number returned in the Oracle error does not correspond to what I’m seeing in TOAD (because of comments, etc.) Is there a way to determine/view the corresponding line numbers of the actual source code being executed?

What does your error message look like?

I have a procedure that deletes a row in a table. I made the table read-only to cause an error when the procedure is called. The error is shown below, and you can see the line number:

ORA-14466: Data in a read-only partition or subpartition cannot be modified.
ORA-06512: at “JDORLON.DML_TABLES”, line 814
ORA-06512: at line 2

This is the actual error:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at “COMPAS.PKG_ANNUAL_PAYER”, line 538

ORA-06512: at line 1

Ok, so if you do a spool SQL, you’ll probably see a call to COMPAS.PKG_ANNUAL_PAYER on line 1.

If you look at the COMPAS.PKG_ANNUAL_PAYER package, whatever SQL that’s run on line 538 is what caused the error.

maybe if you have formatting turned on in Toad, it changed the line numbers…

Ok, so line 538 is a RAISE statement within an error block, but I questioned whether or not that was the correct line since I’ve always thought you would have to exclude comments, etc., and this is a huge package so I didn’t want to try to start doing that. Thanks for the feedback, John.

Oh, yeah RAISE would be where the error occurs. You can, if you want, log call stack info from your code. Take a look at this:

www.oracle.com/…/o14plsql-2045346.html

This is great info, thanks again John.