The function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE is a great improvement to PL/SQL and adds a much needed functionality. Another approach is to call the function in the exception section of the block in which the error was raised. Later on, I will explain this in more detail. Regards Michel Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325217 is a reply to message #325210] Thu, this page
In many applications, however, we work to avoid unhandled exceptions. Therefore, an important distinction needs to be made between application code that needs to be logged and that which doesn't. By working at some of Belgiums largest companies during this period, Jan has gained a tremendous insight in Oracle internals, making him an expert when it comes to performance tuning, data Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325185 is a reply to message #325182] Thu, 05 June Continued
Tags: Exceptions Jan Leers Jan Leers is an Oracle Certified Professional/Expert, working as an Oracle Consultant for over 5 years. When you trap the exception you need to use dbms_utility.format_error_stack. Just the Line Number, Please In a real-world application, the error backtrace could be very long. Regards Michel Report message to a moderator Previous Topic: sql query problem Next Topic: execute immediate with nvarchar data type Goto Forum: - SQL & PL/SQLSQL
To raise a user defined error with a chosen number and error message, we call the procedure “RAISE_APPLICATION_ERROR”. Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325208 is a reply to message #325198] Thu, 05 June more stack exchange communities company blog Stack Exchange Inbox Reputation and Badges sign up log in tour help Tour Start here for a quick overview of the site Help Center Detailed Pl Sql Call Stack It displays the call stack at the point where an exception was raised, even if the function is called in a PL/SQL block in an outer scope from that where the
One of them is throwing an ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception. Avoid exception handlers in intermediate programs in your stack, and call the backtrace function in the exception section of the outermost program in your stack. The "ORA-06512" error is not included, but this is implied because it is a backtrace message. Another approach is to call the function in the exception section of the block in which the error was raised.
SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Error Stack Start ***** Depth Error Error . What Are The Methods There In Save Exceptions In Oracle Cary Millsap's latest book The Method R Guide to Mastering Oracle Trace Data, Second Edition contains the richest description of Oracle extended SQL trace data that you’ll ever find, and over As the nesting of exception blocks increases, so does the amount of information the new function provides, as the following example demonstrates (note that this time I have not re-raised the These will be captured and logged by the business-rule packages that process data and need to write to application log files.
The long awaited Oracle enhancement to solve this problem was introduced in the first release of 10g. http://stackoverflow.com/questions/1589252/is-there-a-way-to-get-the-line-number-where-an-exception-was-thrown CREATE OR REPLACE PROCEDURE display_backtrace AS l_depth PLS_INTEGER; BEGIN l_depth := UTL_CALL_STACK.backtrace_depth; DBMS_OUTPUT.put_line('***** Backtrace Start *****'); DBMS_OUTPUT.put_line('Depth BTrace BTrace'); DBMS_OUTPUT.put_line('. Pl/sql Line Number Our first message tells us a “no data found”-error occurred, our second message tells us we had two errors, first the ORA-01403, followed by the user-defined ORA-20001. Oracle Error Stack Trace Home | Articles | Scripts | Blog | Certification | Misc | About About Tim Hall Copyright & Disclaimer oracle-developer.net Home Articles 11g New Features 10g New Features 9i New Features
If we do not handle the error, the error ORA-65000, “Unhandled user exception” is propagated. this website Does the code terminate? SQL> CREATE OR REPLACE PROCEDURE p2 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p2'); 5 DBMS_OUTPUT.put_line ('calling p1'); 6 p1; 7 EXCEPTION 8 WHEN OTHERS 9 THEN 10 RAISE NO_DATA_FOUND; 11 When an exception is raised, one of the most important pieces of information a programmer would like to uncover is the line of code that raised the exception. $$plsql_line
The following simple PL/SQL block demonstrates that Oracle will happily tell us where a procedure, function or anonymous block hit an exception. We could easily reverse it to display first to last. -- Procedure to display the call stack. The transaction stays pending unless some PL/SQL code does an explicit COMMIT or ROLLBACK. Get More Info On the other hand, we got this information by letting the exception go unhandled.
The error stack gives us an overview of all the errors that were raised, giving more information than “SQLCODE” and “SQLERRM”. Dbms_utility.format_call_stack Example The call stack will give us information about which code called the procedure or function raising the error. In some cases, exceptions in nested calls result in different errors being produced by the error handler of the calling routine.
Leave a response Cancel Reply → * Required * Required Notify me of followup comments via e-mail. l_depth LOOP DBMS_OUTPUT.put_line( RPAD(i, 10) || RPAD(TO_CHAR(UTL_CALL_STACK.backtrace_line(i),'99'), 10) || UTL_CALL_STACK.backtrace_unit(i) ); END LOOP; DBMS_OUTPUT.put_line('***** Backtrace End *****'); END; / -- Run the test. Code Message --------- --------- -------------------- 5 ORA-01403 no data found 4 ORA-06512 at "TEST.TEST_PKG", line 24 3 ORA-01422 exact fetch returns more than requested number of rows 2 ORA-06512 at "TEST.TEST_PKG", Format_error_stack Vs Format_error_backtrace PL/SQL offers a powerful and flexible exception architecture.
In previous releases this information was displayed using the DBMS_UTILITY.FORMAT_CALL_STACK function, as shown below. -- Procedure to display the call stack. CREATE OR REPLACE PROCEDURE display_error_stack AS BEGIN DBMS_OUTPUT.put_line('***** Error Stack Start *****'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line('***** Error Stack End *****'); END; / -- Test package to show a nested call. SUBPROGRAM : Subprogram name associated with the current call. see here A major caveat to this is, of course, that if we go back to procedure WILL_ERROR and re-raise the exception in a WHEN OTHERS or such-like, we will once again lose
The developer of the application might even like to display that critical information to the users so that they can immediately and accurately report the problem to the support staff. They are the ones that honestly say what is wrong with your program. share|improve this answer answered Oct 20 '09 at 8:30 Jeffrey Kemp 37k859104 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google SQL> CREATE OR REPLACE PROCEDURE p3 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p3, calling p2'); 5 p2; 6 EXCEPTION 7 WHEN OTHERS 8 THEN 9 DBMS_OUTPUT.put_line ('Error stack from p3:');
Code Message'); DBMS_OUTPUT.put_line('--------- --------- --------------------'); FOR i IN REVERSE 1 .. The function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE is a great improvement to PL/SQL and adds a much needed functionality. The two backtraces are: "ORA-06512: at line 5 ORA-06512: at line 11 ORA-06512: at line 17″ And "ORA-06512: at line 21 ORA-06512: at line 27 ORA-06512: at line 30″ The first Senior MemberAccount Moderator Put the step in a package variable, a context, dbms_application_info, each one these can be queried by the caller, you can even but the whole stack but don't
Like Show 0 Likes(0) Actions 3. Please type your message and try again. BACKTRACE_LINE : Line number in the subprogram of the current call.