Display Line Number Oracle 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 the line number is oracle error line number wrong to let the exception go unhandled in your PL/SQL code. In Oracle Database 10g Release 1
How To Get Error Line Number In Oracle
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 stack of programs oracle show line numbers 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
Exception No Data Found Oracle
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; 4 END; 5 / BEGIN dbms_utility.format_error_backtrace example in oracle * 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.P1", line 5. In the above example, the call to DBMS_UTILITY.FORMAT_ERROR_BACKT
is a PLS_INTEGER literal value indicating the line number reference to $$PLSQL_LINE in the current program unit. In
Oracle Error Stack Trace
other words, $$PLSQL_LINE is the number of the line where $$PLSQL_LINE appears
How To Find Which Line Error Was Raised
in your PL/SQL code. Another useful predefined inquiry directive is PLSQL_UNIT which is a VARCHAR2 literal value indicating what are the methods there in save exceptions in oracle the current source program unit. For a named compilation unit, $$PLSQL_UNIT contains the unit name. For an anonymous block, $$PLSQL_UNIT is NULL. Now, on to some examples: From an http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ anonymous block: EDDIE@XE> BEGIN 2 DBMS_OUTPUT.put_line ('Line number: ' 3 || $$plsql_line); 4 DBMS_OUTPUT.put_line ('Unit: ' 5 || COALESCE ($$plsql_unit, 'anonymous block') 6 ); 7 END; 8 / Line number: 3 Unit: anonymous block From a procedure: EDDIE@XE> CREATE OR REPLACE PROCEDURE my_proc 2 IS 3 BEGIN 4 DBMS_OUTPUT.put_line ('Line number: ' 5 || $$plsql_line); 6 DBMS_OUTPUT.put_line ('Unit: ' 7 http://awads.net/wp/2006/08/03/heres-a-quick-way-to-get-the-line-number-in-plsql/ || $$plsql_unit 8 ); 9 END; 10 / Procedure created. EDDIE@XE> exec my_proc; Line number: 5 Unit: MY_PROC From a package: EDDIE@XE> CREATE OR REPLACE PACKAGE my_pkg 2 IS 3 PROCEDURE my_proc; 4 END; 5 / Package created. EDDIE@XE> 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 9 ); 10 END; 11 END; 12 / Package body created. EDDIE@XE> exec my_pkg.my_proc; Line number: 6 Unit: MY_PKG Note that $$PLSQL_UNIT returned the package name, not the procedure name. A few days ago, I blogged about how to find where an error was raised in PL/SQL using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which returns a formatted string that displays a stack of programs and line numbers. But, you must parse the returned string to find the line number and program unit name if you want to use them elsewhere in your programs (like storing them in a log table or using them to query the user_source data dictionary table for example). $$PLSQL
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting http://stackoverflow.com/questions/2999482/how-does-line-numbering-work-in-an-oracle-trigger ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the http://www.praetoriate.com/t_high_perform_line_numbers.htm Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How does line numbering work in an Oracle trigger? up vote 4 down vote favorite 1 I have a trigger that's throwing an error, and I am not sure how to know which line of PL/SQL code is line number throwing that error. My error is [Oracle]ORA-01403: no data found ORA-06512: at "MYSCHEMA.FOO_BI", line 9 My trigger is something like this: create or replace TRIGGER "MYSCHEMA"."FOO_BI" BEFORE INSERT ON FOO REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE NUM1 NUMBER; NUM2 NUMBER; BEGIN -- some comment if :new.batch_num is null then SELECT COUNT(*) INTO :NEW.BATCH_NUM FROM FOO WHERE CORP_ID = :NEW.CORP_ID; end if; if :new.batch_id is null or :new.batch_id = '' then :NEW.BATCH_ID := :NEW.CORP_ID || '-' || :NEW.BATCH_NUM; end error line number if; /* etc... */ I found what looks like a similar question but the line numbering starts with the create or replace... and represents my error line as a comment, which I think must be bogus. How is the line numbering reported when an error is thrown in execution of a trigger? oracle triggers share|improve this question asked Jun 8 '10 at 16:54 Chris Farmer 12.8k2381134 3 A Nit: In Oracle the empty string is equivalent to null. So or :new.batch_id = '' is equivalent to or :new.batch_id = null which will never return true. The first part :new.batch_id is null is all you need. –Shannon Severance Jun 8 '10 at 17:22 @Shannon: thanks for the tip! I can't claim responsibility for all of this trigger, but I have to confess that part might have been mine. Maybe. –Chris Farmer Jun 8 '10 at 17:28 add a comment| 2 Answers 2 active oldest votes up vote 13 down vote accepted The line numbering (as reported in stack traces) starts with the DECLARE being line 1. So, if you do the following: CREATE OR REPLACE TRIGGER foo BEFORE INSERT ON test1 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE n1 NUMBER := 1; n2 NUMBER := 2; BEGIN -- a comment IF :new.n1 IS NULL THEN n1 := n2/0; END IF; END; / SQL> insert into test1 values (3,'XX','YY',NULL); insert into test1 values (3,'XX','YY',NULL) ORA-01476: divisor is equal to ze
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: identifier ‘ACTIVE