Pl/sql Error Location
Contents |
TECHNOLOGY: PL/SQL Tracing Lines By Steven Feuerstein Find and report your errors—by line number—in Oracle Database 10g. PL/SQL how to find error line number in pl/sql offers a powerful and flexible exception architecture. Of course, there is how to find which line error was raised in oracle always room for improvement, and in Oracle Database 10g, exception handling takes a big step forward dbms_utility.format_error_backtrace example in oracle with the introduction of the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function. This article explores the problem that this function solves and how best to use it. Who Raised That Exception?
Oracle Error Stack Trace
When an exception is raised, one of the most important pieces of information a programmer would like to uncover is the line of code that raised the exception. Prior to Oracle Database 10g, one could obtain this information only by allowing the exception to go unhandled. Let's revisit the error-handling behavior available pl/sql line number to programmers in Oracle9i Database. 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. Notice that there is no error handling in any of the procedures; it is most significantly lacking in the top-level proc3 procedure. If I run proc3 in SQL*Plus, I will see the following results: ERROR at line 1: ORA-01403: no data found ORA-06512: at "SCOTT.PROC1", line 4 ORA-06512: at "SCOTT.PROC2", line 6 ORA-06512: at "SCOTT.PROC3", line 4 ORA-06512: at line 3 Code Listing 1: A stack of procedures CREATE OR REPLACE PROCEDURE proc1 IS BEGIN DBMS_OUTPUT.put_line ('running proc1'); RAISE NO_DATA_FOUND; END; / CREATE OR REPLACE PROCEDURE proc2 IS l_str VARCHAR2(30) := 'calling proc1'; BEGIN DBMS_OUTPUT.put_line (l_str); proc1; END; / CREATE OR REPLACE PROCEDURE proc3 IS BEGIN DBMS_OUTPUT.put_line ('calling proc2'); proc2; END; / This is the error trace dump of an unhandled exception, and it shows t
March 2012 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 January 2012 March 2012 May 2012 July 2012 September 2012 November 2012 As Published In March/April 2012 TECHNOLOGY: PL/SQL Error Management By pl sql exception handling examples Steven Feuerstein Part 6 in a series of articles on understanding and
Pl Sql Call Stack
using PL/SQL Even if you write absolutely perfect PL/SQL programs, it is possible and even likely that something will go wrong
Format_error_stack Vs Format_error_backtrace
and an error will occur when those programs 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 http://www.oracle.com/technetwork/testcontent/o25plsql-093886.html 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 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 http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22plsql-1518275.html 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/SQL and is raised when a unique index constraint is violated. A user-defined exception is one you have declared in the declaration section of a program unit. User-defined exceptions can be associated with an internally defined exception (that is, you can give a name to an otherwise unnamed exception) or with an application-specific error. Every exception has an error code and an error message associated with it. Oracle Database provides functions for retrieving these values when you a
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 http://stackoverflow.com/questions/16183290/oracle-find-the-position-of-an-error-in-dynamic-sql-using-sql-or-pl-sql Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Oracle: Find the position of an error in dynamic SQL using SQL or PL/SQL up vote 6 how to down vote favorite 2 How can I find the position of an error in a Dynamic SQL statement in PL/SQL or SQL? From SQL*Plus I see the position of an error in, for example, an invalid SQL DML statement: SYS@orcl> SELECT 2 X 3 FROM 4 TABLEX 5 / TABLEX * ERROR at line 4: ORA-00942: table or view does not exist SQL*Plus shows the error with the line number, and how to find prints and marks that line with an asterisk where the error is found. Converting to Dynamic SQL, I can get the error code (SQLCODE) and error message (SQLERRM): SYS@orcl> SET SERVEROUTPUT ON SYS@orcl> BEGIN 2 EXECUTE IMMEDIATE 'SELECT X FROM TABLEX'; 3 EXCEPTION 4 WHEN OTHERS THEN 5 DBMS_OUTPUT.PUT_LINE('SQLCODE:' || SQLCODE); 6 DBMS_OUTPUT.PUT_LINE('SQLERRM:' || SQLERRM); 7 END; 8 / SQLCODE:-942 SQLERRM:ORA-00942: table or view does not exist But how do I get the position of the error in the Dynamic SQL string? I see that Oracle provides a SQL Communications Area (SQLCA) that contains interesting information about an error. In particular: the SQLCODE and SQLERRM fields (that might be the source of the data retrieved with the respective PL/SQL functions), the SQLERRD field where the SQLERRD(5) element that gives the 'parse error offset'. Is it possible to access SQLERRD from PL/SQL or SQL? If so, how? If not, what other technique can give the location of the error from PL/SQL or SQL? (Here http://docs.oracle.com/cd/B28359_01/appdev.111/b31231/chapter8.htm#BABIGBFF the SQLCA is documented and accessed with Pro*C.) (The answer here how to declare SQLCA.SQLERRD? seems to indicate that SQLERRD is not defined in PL/SQL and therefore not accessible.) (The discussion here Why doesn't Oracle tell you WHICH table or view does not exist? gives some suggestions to sh