Exception, Exception, Where Did You Raise

Question:
When capturing errors in an exception handler, is there a way to capture the line of code where the error occurred?
Answer:
Ah, Suneel; such an easy question to ask, but prior to Oracle 10g, such a painful one to answer!
You see, for years, Oracle has provided a program named DBMS_UTILITY.FORMAT_ERROR_STACK that would seem to address your problem, but in fact does not. A call to this function was never intended to provide you with the line number on which an error was first raised in your application code; instead, it provides information about the error raised—essentially all the stuff that went wrong "under" that point.
What you want is the "back trace": information about how you got to that point in your code, and at what point precisely.
In fact, it turns out that (again, prior to the advent of Oracle 10g) that if you handled the error in PL/SQL in any way whatsoever, you would lose the information about the line number at which the error originated. The only way to see the line number in question (in error) was to allow the exception to go unhandled. SQL*Plus would then display both the error stack and the back trace on that error, blending the information together and including—most important—line numbers. For this reason, I have written code that looks like this:
/* REMOVE EXCEPTION SECTION TO SEE LINE WITH ERROR: */
EXCEPTION
WHEN OTHERS
THEN
collect_garbage_finale;
RAISE;
--*/
END my_program;
What's the point? Well, if I really, really need to see that line number, all I have to do is remove the "*/" from the end of that first line and then the entire exception section is commented out. I then run my code and the error goes unhandled!
Pretty slick, huh? Yeah, right.
Fortunately, in Oracle 10g, our dear friends over at PL/SQL Central have given us a new program: DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, to which I will refer affectionately as "Backtrace" in the rest of this answer.
Backtrace gives us precisely the information we've desired for years. The following script demonstrates its usefulness. As you will quickly surmise, I create three procedures imaginatively named proc1, proc2, and proc3. In this scenario, proc3 calls proc2, which calls proc1, which promptly raises an exception. Only proc3 has an exception section, in which I take advantage of Backtrace:
1 CREATE OR REPLACE PROCEDURE proc1
2 IS
3 BEGIN
4 -- Inserting some filler here
5 -- to make the line numbers different!
6 DBMS_OUTPUT.put_line ('running proc1');
7 RAISE NO_DATA_FOUND
8* END;
/
1 CREATE OR REPLACE PROCEDURE proc2
2 IS
3 l_str VARCHAR2(30)
4 := 'calling proc1';
5 BEGIN
6 DBMS_OUTPUT.put_line (
7 l_str);
8 proc1;
9* END;
/
1 CREATE OR REPLACE PROCEDURE proc3
2 IS
3 BEGIN
4 DBMS_OUTPUT.put_line ('calling proc2');
5 proc2;
6 EXCEPTION
7 WHEN OTHERS
8 THEN
9 DBMS_OUTPUT.put_line ('Error stack at top level:');
10 DBMS_OUTPUT.put_line (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
11* END;
/
And now I run proc3 to get the whole thing rolling:
BEGIN
DBMS_OUTPUT.PUT_LINE ('Proc3 -> Proc2 -> Proc1');
proc3;
END;
/
And the results from running this script:
Proc3 -> Proc2 -> Proc1
calling proc2
calling proc1
running proc1
Error stack at top level:
ORA-06512: at "SCOTT.PROC1", line 7
ORA-06512: at "SCOTT.PROC2", line 8
ORA-06512: at "SCOTT.PROC3", line 5

And we can immediately see that the error was raised on line 7 of proc1. Two things to keep in mind:
• Backtrace does not provide the actual error number; that information is still provided by DBMS_UTILITY.FORMAT_ERROR_STACK. You will, consequently, generally want to call these two functions together, to obtain all the information you need when an error occurs.
• If your program is defined in a package, Backtrace will provide the name of the package, but not that of the program itself; after compilation, the names of your programs are replaced with pointers, and the names themselves are "lost."
To help you use Backtrace, bt.pkg download offers a function that takes the output from Backtrace and returns a record containing the parsed information from the very top of the Backtrace stack: the program name and line number for the exception that stopped your program from continuing.
Hats off to Christopher Racicot (Oracle PL/SQL Development Manager) and everyone else on the PL/SQL development team for listening to PL/SQL users around the world and giving us Backtrace!