Pl Sql Show Error Line
Contents |
March 2012 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 January 2012 March 2012 May 2012 July 2012 September 2012
How To Find Which Line Error Was Raised In Oracle
November 2012 As Published In March/April 2012 TECHNOLOGY: PL/SQL Error pl/sql line number Management By Steven Feuerstein Part 6 in a series of articles on understanding and using PL/SQL
Oracle Error Stack Trace
Even if you write absolutely perfect PL/SQL programs, it is possible and even likely that something will go wrong and an error will occur when those programs pl sql call stack are run. How your code responds to and deals with that error often spells the difference between a successful application and one that creates all sorts of problems for users as well as developers. This article explores the world of error management in PL/SQL: the different types of exceptions you may encounter; when, why, and $$plsql_line how exceptions are raised; how to define your own exceptions; how you can handle exceptions when they occur; and how you can report information about problems back to your users. Exception Overview There are three categories of exceptions in the world of PL/SQL: internally defined, predefined, and user-defined. An internally defined exception is one that is raised internally by an Oracle Database process; this kind of exception always has an error code but does not have a name unless it is assigned one by PL/SQL or your own code. An example of an internally defined exception is ORA-00060 (deadlock detected while waiting for resource). A predefined exception is an internally defined exception that is assigned a name by PL/SQL. Most predefined exceptions are defined in the STANDARD package (a package provided by Oracle Database that defines many common programming elements of the PL/SQL language) and are among the most commonly encountered exceptions. One example is ORA-00001, which is assigned the name DUP_VAL_ON_INDEX in PL
is very important to find the line number on which the error had occurred. The question is how
What Are The Methods There In Save Exceptions In Oracle
to find that line number. Before Oracle Database 10g Release 1, the format_error_stack vs format_error_backtrace only way to know the line number is to let the exception go unhandled in your PL/SQL code. In
Dbms_utility.format_error_backtrace 11g
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 stack of programs and http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22plsql-1518275.html 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 BEGIN 4 DBMS_OUTPUT.put_line ('in p2'); 5 DBMS_OUTPUT.put_line http://awads.net/wp/2006/07/25/how-to-find-where-an-error-was-raised-in-plsql/ ('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_OUTPUT.put_line ('in p3, calling p2'); 5 p2; 6 EXCEPTION 7 WHEN OTHERS 8 THEN 9
that comes up fairly frequently revolves around how to see your errors when working with PL/SQL in SQL Developer. Most folks are probably working in the http://www.thatjeffsmith.com/archive/2012/01/viewing-plsql-compilation-errors-in-oracle-sql-developer/ worksheet - this is the default editor for your connection. Let's take a look at this sample program CREATE OR REPLACE PROCEDURE do_nothing IS BEGIN dbms_output.put(sysdate); this should probably error OUT, RIGHT? NULL; END; / If we were to create this procedure, we would probably expect some errors. So let's run this in the Worksheet. I'm using Ctrl+Enter pl sql to execute this single statement. I have warnings, oh no! Ok, but how do I see the errors? This is the worksheet. The commands run here will run very similar to how they would run in SQL*Plus. So knowing this, if we change-up the process a little bit, we can start to get better feedback from SQL Developer. pl sql show Add ‘show errors' after the create or replace, and use F5 instead of Ctrl+Enter. This will run the entire script, and ask Oracle to show us any errors for the session. A little better, but not as good as it gets You might be wondering why the line number is off. Oracle reports back a problem on line #4. If you'll notice our program starts on SQL Developer worksheet line #2, but Oracle database only receives the actual statement, so you can do the math here to figure out the actual problem lies on line #5. Viewing Errors in the Explorer The current errors for the objects can also be viewed in the database explorer. Navigate to the object and open the ‘Errors' panel. Viewing Errors in the Explorer Working in the Procedure Editor As the name implies, the Procedure Editor is for developing and debugging your PL/SQL code. The full power of the IDE is realized here. Ok, so how do you get started with a Procedure Editor instead of a Workshee