Oracle Pl/sql Compile Error Table
Contents |
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 Steven Feuerstein Part 6 pl sql exception handling examples in a series of articles on understanding and using PL/SQL Even if you write
Oracle Raise Exception With Message
absolutely perfect PL/SQL programs, it is possible and even likely that something will go wrong and an error will occur when those pl sql exception handling best practices 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 of problems for users as well as developers. This pl sql continue after exception 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 the world of PL/SQL: internally defined, predefined, and user-defined. An internally defined exception is
Oracle Sqlerrm
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 are handling an exception (see Table 1). Description How to Get It The error code. This code is useful when you need to look up generic information about what
program to continue to operate in the presence of errors. Topics: Overview of PL/SQL Run-Time Error Handling Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions Advantages of PL/SQL Exceptions Predefined PL/SQL Exceptions error table in oracle Defining Your Own PL/SQL Exceptions How PL/SQL Exceptions Are Raised How PL/SQL Exceptions dml error logging in oracle 11g Propagate Reraising a PL/SQL Exception Handling Raised PL/SQL Exceptions Overview of PL/SQL Compile-Time Warnings Overview of PL/SQL Run-Time Error Handling In
Pl Sql Logging Example
PL/SQL, an error condition is called an exception. An exception can be either internally defined (by the run-time system) or user-defined. Examples of internally defined exceptions are ORA-22056 (value string is divided by zero) http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22plsql-1518275.html and ORA-27102 (out of memory). Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names. You can define your own exceptions in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. User-defined exceptions must be given names. When an error occurs, an exception https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements or invocations of the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR. To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment. For information about managing errors when using BULK COLLECT, see Handling FORALL Exceptions (%BULK_EXCEPTIONS Attribute). Example 11-1 calculates a price-to-earnings ratio for a company. If the company has zero earnings, the division operation raises the predefined exception ZERO_DIVIDE, the execution of the block is interrupted, and control is transferred to the exception handlers. The optional OTHERS handler catches all exceptions that the block does not name specifically. Example 11-1 Run-Time Error Handling DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN -- Calculation might cause division-by-zero error. pe_ratio := stock_price / net_earnings; DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio); EXCEPTION -- exception handlers begin -- Only one of the WHEN blocks is ex
Error Handling" in Oracle Database PL/SQL Language Reference. See the end of this chapter for TimesTen-specific considerations. The following topics are covered: Understanding exceptions Trapping exceptions Showing errors in ttIsql Differences in TimesTen: exception https://docs.oracle.com/cd/E11882_01/timesten.112/e21639/exceptions.htm handing and error behavior Understanding exceptions This section provides an overview of exceptions in PL/SQL programming, covering the following topics: About exceptions Exception types About exceptions An exception is a PL/SQL error that is raised during http://docstore.mik.ua/orelly/oracle/advprog/ch15_01.htm program execution, either implicitly by TimesTen or explicitly by your program. Handle an exception by trapping it with a handler or propagating it to the calling environment. For example, if your SELECT statement returns multiple pl sql rows, TimesTen returns an error (exception) at runtime. As the following example shows, you would see TimesTen error 8507, then the associated ORA error message. (ORA messages, originally defined for Oracle Database, are similarly implemented by TimesTen.) Command> DECLARE > v_lname VARCHAR2 (15); > BEGIN > SELECT last_name INTO v_lname > FROM employees > WHERE first_name = 'John'; > DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_lname); > END; > / 8507: pl sql exception ORA-01422: exact fetch returns more than requested number of rows 8507: ORA-06512: at line 4 The command failed. You can handle such exceptions in your PL/SQL block so that your program completes successfully. For example: Command> DECLARE > v_lname VARCHAR2 (15); > BEGIN > SELECT last_name INTO v_lname > FROM employees > WHERE first_name = 'John'; > DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_lname); > EXCEPTION > WHEN TOO_MANY_ROWS THEN > DBMS_OUTPUT.PUT_LINE (' Your SELECT statement retrieved multiple > rows. Consider using a cursor.'); > END; > / Your SELECT statement retrieved multiple rows. Consider using a cursor. PL/SQL procedure successfully completed. Exception types There are three types of exceptions: Predefined exceptions are error conditions that are defined by PL/SQL. Non-predefined exceptions include any standard TimesTen errors. User-defined exceptions are exceptions specific to your application. In TimesTen, these three types of exceptions are used in the same way as in Oracle Database. Exception Description How to handle Predefined TimesTen error One of approximately 20 errors that occur most often in PL/SQL code You are not required to declare these exceptions. They are predefined by TimesTen. TimesTen implicitly raises the error. Non-predefined TimesTen error Any other standard TimesTen error These must be declared in the declarative section of your application. TimesTen implicitly raise
which allow you to view both stored source code and compile errors. It provides an alternative to the SQL*Plus SHOW ERRORS command which offers you significantly more information about your compile problem. It allows you to quickly scan PL/SQL source code stored in the data dictionary. This chapter shows you how to use the PLVvu programs and also explores the steps involved in constructing a utility like PLVvu. Before diving into the programs provided by PLVvu, however, let's review the situation most PL/SQL developers around the world face on a daily basis as they try to compile their code. 15.1 Compiling PL/SQL Code in SQL*Plus Suppose that you work really, really hard at building this very complicated PL/SQL program. It's a big one, but you feel as if you've got a handle on it. You "create or replace" it in SQL*Plus and here is what you see: SQL> start bigone.sql Warning: Procedure created with compilation errors. You groan. At the same time, you realize that you weren't very likely to get it all right the first time. Well, it's time to find out what the error is. Fortunately, Oracle Corporation provides a utility to view the compile errors: the SHOW ERRORS command. SQL> show errors LINE/COL ERROR -------- -------------------------------------------------------------- 624/10 PLS-00103: Encountered the symbol "IF" when expecting one of the following: * & = - + ; < / > in mod not rem an exponent (**) <> or != or ~= >= <= <> and or like between is null etc. ; was inserted before "IF" to continue. Wow. Underwhelmed or what? Let's see...so, this error of some kind was found on the tenth character of line 624. Line 624, eh? I open up the file containing my fantastic new program and go down to line 624. Here is what I find: 622 FOR data_rec IN data_cur 623 LOOP 624 restructure (data_rec.key_val); 635 END LOOP; Not an IF in sight. My sense of elation deflates. Getting this program to compile is going to be more difficult that I had thought. What is going on and why am I so depressed? The most critical problem is that SHOW ERRORS does not actually show the line of code upon which the error was found. And even if it d