Oracle Pl/sql Get Line Number Error
Contents |
is very important to find the line number on which the error had occurred. The question is how to find that line number. Before Oracle Database 10g Release 1, the only way to know
How To Find Which Line Error Was Raised In Oracle
the line number is to let the exception go unhandled in your PL/SQL code. In dbms_utility.format_error_backtrace example in oracle Oracle Database 10g Release 1 and above, you can take advantage of the new function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This new function returns a formatted
Oracle Error Stack Trace
string that displays a stack of programs and line numbers leading back to the line on which the error was originally raised. For example, prior to 10gR1: SQL> CREATE OR REPLACE PROCEDURE p1 2 IS 3 BEGIN 4 pl/sql line number DBMS_OUTPUT.put_line ('in p1, raising error'); 5 RAISE VALUE_ERROR; 6 END; 7 / Procedure created. 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 END; 8 / Procedure created. SQL> CREATE OR REPLACE PROCEDURE p3 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p3, calling p2'); 5 p2; 6 END; 7 / Procedure created. Notice the unhandled VALUE_ERROR exception raised in p1. Now, Let's call p3: what are the methods there in save exceptions in oracle SQL> BEGIN 2 DBMS_OUTPUT.put_line ('calling p3'); 3 p3; 4 END; 5 / BEGIN * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "HR.P1", line 5 ORA-06512: at "HR.P2", line 6 ORA-06512: at "HR.P3", line 5 ORA-06512: at line 3 As expected, by not handling the exception, the procedure returns an error and we are able to know where the exception was raised. In this example, the error ORA-06502: PL/SQL: numeric or value error was raised at "HR.P1", line 5. Starting with 10gR1, you can call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function in your exception handler. 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 7 WHEN OTHERS 8 THEN 9 DBMS_OUTPUT.put_line ('Error stack from p3:'); 10 DBMS_OUTPUT.put_line 11 (DBMS_UTILITY.format_error_backtrace); 12 END; 13 / Procedure created. Let's call p3: SQL> set serveroutput on SQL> BEGIN 2 DBMS_OUTPUT.put_line ('calling p3'); 3 p3; 4 END; 5 / calling p3 in p3, calling p2 in p2 calling p1 in p1, raising error Error stack from p3: ORA-06512: at "HR.P1", line 5 ORA-06512: at "HR.P2", line 6 ORA-06512: at "HR.P3", line 5 PL/SQL procedure successfully completed. The procedure p3 successfully completed and returned the execution stack at the point where the exception was raised. The first l
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on
$$plsql_line
and reload this page. Please enter a title. You can not pl sql call stack post a blank message. Please type your message and try again. More discussions in PL/SQL and SQL
Show Line Number In Pl Sql Developer
All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 3 Replies Latest reply on Feb 27, 2010 2:30 PM by Solomon Yakobson Get line number error http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ muttleychess Feb 27, 2010 2:08 PM Hi Is there some way to get line number error when there is some exception ? I use oracle 9.2.02 thank you in advance I have the same question Show 0 Likes(0) 15161Views Tags: none (add) This content has been marked as final. Show 3 replies 1. Re: Get https://community.oracle.com/thread/1037981 line number error sybrand_b Feb 27, 2010 2:12 PM (in response to muttleychess) When you don't trap the exception you will get a line number, of the line in error. When you trap the exception you need to use dbms_utility.format_error_stack. Thid will not provide correct line numbers. This issue has been resolved in 10g or 11g, and 9iR2 is going completely out of support in July. ----------- Sybrand Bakker Senikor Oracle DBA Like Show 0 Likes(0) Actions 2. 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_ERROR_BACKTRACE was introduced in 10g. SY. Like Show 0 Likes(0) Actions 3. Re: Get line number error Solomon Yakobson Feb 27, 2010 2:30 PM (in response to sybrand_b) sybrand_b wrote: When you trap the exception you need to use dbms_utility.format_error_stack. Thid will not provide correct line numbers. This issue has been resolved in 10g or 11g, and 9iR2 is going completely out of support in July.FORMAT_E
is very important to find the line number on which the error had occurred. The question is how to find that line number. Before Oracle Database 10g Release 1, the only way to know the line http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ number is to let the exception go unhandled in your PL/SQL code. In Oracle Database 10g Release 1 and above, you can take advantage of the new function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This new function returns a formatted string that displays a https://oracle-base.com/articles/12c/utl-call-stack-12cr1 stack of programs and line numbers leading back to the line on which the error was originally raised. For example, prior to 10gR1: SQL> CREATE OR REPLACE PROCEDURE p1 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p1, raising error'); line number 5 RAISE VALUE_ERROR; 6 END; 7 / Procedure created. 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 END; 8 / Procedure created. SQL> CREATE OR REPLACE PROCEDURE p3 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('in p3, calling p2'); 5 p2; 6 END; 7 / Procedure created. Notice the unhandled VALUE_ERROR exception raised in p1. Now, Let's call p3: SQL> BEGIN 2 DBMS_OUTPUT.put_line ('calling p3'); 3 p3; oracle pl/sql get 4 END; 5 / BEGIN * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error ORA-06512: at "HR.P1", line 5 ORA-06512: at "HR.P2", line 6 ORA-06512: at "HR.P3", line 5 ORA-06512: at line 3 As expected, by not handling the exception, the procedure returns an error and we are able to know where the exception was raised. In this example, the error ORA-06502: PL/SQL: numeric or value error was raised at "HR.P1", line 5. Starting with 10gR1, you can call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function in your exception handler. 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 7 WHEN OTHERS 8 THEN 9 DBMS_OUTPUT.put_line ('Error stack from p3:'); 10 DBMS_OUTPUT.put_line 11 (DBMS_UTILITY.format_error_backtrace); 12 END; 13 / Procedure created. Let's call p3: SQL> set serveroutput on SQL> BEGIN 2 DBMS_OUTPUT.put_line ('calling p3'); 3 p3; 4 END; 5 / calling p3 in p3, calling p2 in p2 calling p1 in p1, raising error Error stack from p3: ORA-06512: at "HR.P1", line 5 ORA-06512: at "HR.P2", line 6 ORA-06512: at "HR.P3", line 5 PL/SQL procedure successfully completed. The procedure p3 successfully completed and returned the execution stack at the point where the exception was raised. The first line of the stack is where the exception was raised. In this example, it was at "HR.P
Social Links Printer Friendly About Search 8i | 9i | 10g | 11g | 12c | 13c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux Home » Articles » 12c » Here UTL_CALL_STACK : Get Detailed Information About the Currently Running Subprogram in Oracle Database 12c Release 1 (12.1) Oracle database 12c introduced the UTL_CALL_STACK package to allow programmatic access to the call stack and error stack, giving much greater flexibility for debugging and error handling of PL/SQL code. This is only a replacement for the existing functionality if you need the extra level of control. The existing functionality in the DBMS_UTILITY package is still available and has not been deprecated. 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. 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_call_stack AS BEGIN DBMS_OUTPUT.put_line('***** Call Stack Start *****'); DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack); DBMS_OUTPUT.put_line('***** Call Stack End *****'); END; / -- Test package to show a nested call. 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; END; PROCEDURE proc_2 AS BEGIN proc_3; END; PROCEDURE proc_3 AS BEGIN display_call_stack; END; END; / -- Run the test. SET SERVEROUTPUT ON EXEC test_pkg.proc_1; ***** Call Stack Start ***** ----- PL/SQL Call Stack ----- object line object handle number name 0xb6d4ac18 4 procedure TEST.DISPLAY_CALL_STACK 0xb6d14298 15 package body TEST.TEST_PKG 0xb6d14298 10 package body TEST.TEST_PKG 0xb6d14298 5 package body TEST.TEST_PKG 0xb99fe7c8 1 anonymous block ***** Call Stack End ***** PL/SQL procedure successfully completed. SQL> As you can see, the output from the DBMS_UTILITY.FORMAT_CALL_STACK function is rather ugly and we have no control over it, other than to manually parse it. The UTL_CALL_STACK package contains APIs to display the contents of the call stack in a more readable form. DYNAMIC_DEPTH : The number of subprograms on the call stack, starting at the current position in the call stack, to the initial call. LEXICAL_DEPTH : Lexical depth of the subprogram within the current call. UNIT_LINE : Line number in the subprogra