Oracle Sql Error Line Number
Contents |
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 and reload this page. Please enter a title. You can not post a blank message. Please type how to find which line error was raised in oracle your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application
Dbms_utility.format_error_backtrace Example In Oracle
DevelopmentPL/SQL and SQL This discussion is archived 3 Replies Latest reply on Feb 27, 2010 2:30 PM by Solomon Yakobson Get line pl/sql line number number error 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
What Are The Methods There In Save Exceptions In Oracle
same question Show 0 Likes(0) 15170Views Tags: none (add) This content has been marked as final. Show 3 replies 1. Re: Get 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 oracle error stack trace 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_ERROR_STACK will not provide any line numbers: SQL> select * from v$version 2 / BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for 32-bit Windows: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production SQL> set serveroutput on format word_wrapped SQL> begin 2 raise no_data_found; 3 exception 4 when no_data_found 5 then 6 dbms_output.put_line('ERROR_STACK: ' || DBMS_UTILITY.FORMAT_ERROR_STACK); 7 dbms_output.put_line('ERROR_BACKTRACE: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); 8 end; 9 / ERROR_STACK: ORA-01403: no data found ERROR_BACKTRACE: ORA-06512: at line 2 PL/SQL procedure successfully complet
is very important to find the line number on which the error had occurred. The question
$$plsql_line
is how to find that line number. Before Oracle Database 10g
Pl Sql Call Stack
Release 1, the only way to know the line number is to let the exception go unhandled in dbms_utility.format_call_stack example 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 https://community.oracle.com/thread/1037981 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 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 http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ 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; 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_O
E-mail to friend How to get Error Line Number in PL/SQL in Exception Block [message #325159] Thu, 05 June 2008 03:17 rajatratewal Messages: 507Registered: March 2008 Location: INDIA http://www.orafaq.com/forum/t/119924/2/ Senior Member When we use exception block in pl/sql and use SQLERRM it displays only the error details but not the exact line number where error has occured. But if we don't http://www.praetoriate.com/t_high_perform_line_numbers.htm use exception block line number is also displayed. How to get Error Line Number in pl/sql exception block. Is their any method to achieve this in 9i. 10g we have DBMS_UTILITY Backtrace in oracle procedure to achieve this. Any ideas?? Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325173 is a reply to message #325159] Thu, 05 June 2008 04:26 Michel Cadot Messages: 63912Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator Of course, the first question should be why do you use sqlerrm? oracle sql error "When others then dbms_output.put_line(sqlerrm)"? Regards Michel [Updated on: Thu, 05 June 2008 04:30]Report message to a moderator Re: How to get Error Line Number in PL/SQL in Exception Block [message #325182 is a reply to message #325173] Thu, 05 June 2008 04:38 rajatratewal Messages: 507Registered: March 2008 Location: INDIA Senior Member Yes you are right Michel I am using it with others clause. I can replace it with built in or custom exceptions like NO_DATA_FOUND etc. But my question is How to get the Error line number that is causing the exception to throw. If we run a pl/sql procedure without exception block then in Sql* plus we can see the line number where error has occured. If our procedure is small it is very easy to check the statement that is causing error.But if it is quite large then it is not possible to check every line in debug mode.because in b/w begin and end clause we can have number of statements and one exception block to handle all the exceptions. Is their any method available in oracle 9i by which i can trace the stateme
position number. SQL*Plus skips over blank lines when compiling code, so you’ll need to determine the line of code to which the line number refers. This is done by using the list command, as shown in Figure 8.4. Figure 8.4Using the list command to find a line of code. If you like, you can also use the list command to display a range of lines. For example, list 10 15 displays lines 10 through 15 of your source code. If you specify just one line number, list will only display that line. For instance, list 10 displays only line 10 of your code. Using the list command without specifying a line number instructs SQL*Plus to display the entire contents of the buffer. When The Line Number Is Wrong Oracle reports the line number on which an error is detected. It’s not uncommon for the reported line number to be incorrect, because you’ve done something else incorrectly in your code that has no effect until Oracle tries to compile the line number specified in the output of the show errors command. Most of the time, incorrect line numbers are the result of variable and type declaration problems, or as a result of incorrect references to objects or variables. Consider again the code for the Calculate_Student_Grades() procedure, presented in Listing 8.4. Listing 8.4 The Calculate_Student_Grades() procedure. CREATE OR REPLACE PROCEDURE Calculate_Student_Grades IS CURSOR Active_Students_cur IS SELECT ssn FROM STUDENTS WHERE graduation_date IS NOT NULL; Active_Student_rec Active_Students_cur%ROWTYPE; vCurrentSSN STUDENTS.ssn%ROWTYPE; nNewGPA STUDENTS.gpa%TYPE; BEGIN FOR Active_Student_rec IN Active_Student_cur LOOP nNewGPA := Calculate_GPA (vSSN => vCurrentSSN); END LOOP; END Calculate_Student_Grades; / Attempting to compile this code generates three errors. The line and position numbers of these errors are shown in Table 8.1. Table 8.1 Compile errors for the Calculate_Student_Grades() procedure. Line Number Position Error 9 31 PLS-00310: with %ROWTYPE attribute, ‘STUDENTS.SSN’ must name a table, cursor or cursor-variable 10 40 PLS-00302: component ‘GPA’ must be declared 12 30 PLS-00201: ident