Home > Pl Sql > Pl Sql Error Handling Line Number

Pl Sql Error Handling Line Number

Contents

Line Unit'); DBMS_OUTPUT.put_line('--------- --------- --------------------'); FOR i IN REVERSE 1 .. In Oracle Database 10g Release 1 and above, you can take advantage of the new function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. Call Stack Error Stack Backtrace Call Stack The call stack allows you to identify exactly where you are in the currently running code, which includes information about nesting of subprogram calls. SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Call Stack Start ***** Depth Lexical Line Owner Edition Name . http://iipseconline.com/pl-sql/pl-sql-error-line-number.html

This shows the propagation of the exception, which allows you to identify the actual line that caused the problem. END; Besides user defined errors, we could also raise one of the predefined errors. This allows us to create 1000 unique error codes for our logical errors throughout our application. Depth Number --------- --------- --------- --------- --------- -------------------- 1 0 13 TEST DISPLAY_CALL_STACK 2 1 15 TEST TEST_PKG.PROC_3 3 1 10 TEST TEST_PKG.PROC_2 4 1 5 TEST TEST_PKG.PROC_1 5 0 1 http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html

Pl/sql Line Number

Therefore, to ensure that the exception is logged, the following "pseudo-approach" is taken by many developers (note that in the simple examples that follow I've substituted DBMS_OUTPUT.PUT_LINE for an application logging The output includes the procedure names in the package as well as the associated line numbers of the calls. SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Backtrace Start ***** ORA-06512: at "TEST.TEST_PKG", line 18 ORA-06512: at "TEST.TEST_PKG", line 13 ORA-06512: at "TEST.TEST_PKG", line 5 ***** Backtrace End ***** PL/SQL procedure successfully Now that we have the line number, we can zoom right in on the problem code and fix it.

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 SQL> Backtrace Backtrace shows a walk through the call stack from the line where the exception was raised, to the last call before the exception was trapped. BEGIN RAISE no_data_found; EXCEPTION WHEN no_data_found THEN ... $$plsql_line to know the precise point at which a block of code failed.

ERROR_MSG : The error message associated with the current line in the error stack. How To Find Which Line Error Was Raised? The biggest problem I've found is that the pcode doesn't include blank lines and in long blocks the line numbers can get seriously out of whack. Eddie Awad | 25 Jul 2006 12:49 pm Amihay, that will be the subject of another blog post. http://stackoverflow.com/questions/1589252/is-there-a-way-to-get-the-line-number-where-an-exception-was-thrown CREATE OR REPLACE PACKAGE test_pkg AS PROCEDURE proc_1; PROCEDURE proc_2; PROCEDURE proc_3; END; / CREATE OR REPLACE PACKAGE BODY test_pkg AS PROCEDURE proc_1 AS BEGIN proc_2; EXCEPTION WHEN OTHERS THEN display_error_stack;

Is their no other means by which we can achieve this. Pl Sql Call Stack Thick Database Avoid UTL_FILE_DIR Security Weakness - Use Oracle Directories Instead Tags10g 11g acquisition aggregator apex blog book concepts database dbms_scheduler Documentation EBS extension feed Firefox function funny Google gotcha join 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. Code Message'); DBMS_OUTPUT.put_line('--------- --------- --------------------'); FOR i IN REVERSE 1 ..

How To Find Which Line Error Was Raised?

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. Linked 0 How do I get the error message in Oracle SQL Developer to display the line number? Pl/sql Line Number Like Show 0 Likes(0) Actions 3. What Are The Methods There In Save Exceptions In Oracle 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

Although it is recommended to use proper a description for your errors, instead of recycling error codes. news Please turn JavaScript back on and reload this page. Let's see what happens when I add an exception section to the proc3 procedure and then display the error information (the simplest form of error logging). Line Unit --------- --------- -------------------- 3 18 TEST.TEST_PKG 2 13 TEST.TEST_PKG 1 5 TEST.TEST_PKG ***** Backtrace End ***** PL/SQL procedure successfully completed. Oracle Error Stack Trace

I have placed all of this code into a separate initialization procedure in Listing 5. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE This procedure displays the call stack at the point where an exception was raised, even if the procedure is called from an exception handler in an outer scope. You can also subscribe without commenting. have a peek at these guys Depending on the technology used, you might want to use your own logic to retrieve the application user instead of the Oracle user.

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 Show Line Number In Pl Sql Developer [email protected]> CREATE OR REPLACE PACKAGE BODY my_pkg 2 IS 3 PROCEDURE my_proc 4 IS 5 BEGIN 6 DBMS_OUTPUT.put_line ('Line number: ' || $$plsql_line); 7 DBMS_OUTPUT.put_line ( 'Unit: ' 8 || $$plsql_unit Let's use this function in the exception section of procedure p3: SQL> CREATE OR REPLACE PROCEDURE p3 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p3, calling p2'); 5 p2; 6 EXCEPTION

Counterintuitive polarizing filters Where's the 0xBEEF?

END; Transactions stay pending when errors are raised, it is our own responsibility to rollback or commit, although most frameworks will rollback an unhandled exception themselves. Consider this simple chain of program calls in Listing 1: procedure proc3 calls proc2 calls proc1 , at which point proc1 raises the NO_DATA_FOUND exception. Re: Get line number error Solomon Yakobson Feb 27, 2010 2:17 PM (in response to muttleychess) Not in 9.2, AFAIK. Dbms_utility.format_call_stack Example Here is the second version of proc3 : CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; EXCEPTION WHEN OTHERS THEN my_putline ( DBMS_UTILITY.FORMAT_ERROR_STACK); END; / Notice that I

Therefore, an important distinction needs to be made between application code that needs to be logged and that which doesn't. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE was introduced in 10g. if (λ x . check my blog 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

Home Oracle Stuff OraNA Presentations About me Contact me Eddie Awad's Blog News, views, tips and tricks on Oracle and other fun stuff How to find where an error was This tool uses JavaScript and much of it will not work correctly without it enabled. Comments Trackbacks 4 Comments Stew Ashton 08/12/2013 · Reply To be precise, the transaction stays pending but the statement is rolled back automatically. Members Search Help Register Login Home Home» SQL & PL/SQL» SQL & PL/SQL» How to get Error Line Number in PL/SQL in Exception Block (Oracle 9i,9.2.0.6.0,Windows XP) Show: Today's Messages ::

This way you have (and can log) that critical line number, even if the exception is re-raised further up in the stack. EXCEPTION WHEN too_many_rows THEN ...