It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

Exceptions - PL/SQL Run time Errors:

In PL/SQL, an error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user defined. Examples of internally defined exceptions include division by zero and 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 exceptions of your own 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. Unlike internal exceptions, user-defined exceptions must be given names.
When an error occurs, an exception 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, which can also raise predefined exceptions.
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.

Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions

Because reliability is crucial for database programs, use both error checking and exception handling to ensure your program can handle all possibilities:
  • Add exception handlers whenever there is any possibility of an error occurring. Errors are especially likely during arithmetic calculations, string manipulation, and database operations. 
  • Add error-checking code whenever you can predict that an error might occur if your code gets bad input data. Expect that at some time, your code will be passed incorrect or null parameters, that your queries will return no rows or more rows than you expect.
  • Make your programs robust enough to work even if the database is not in the state you expect. For example, perhaps a table you query will have columns added or deleted, or their types changed. You can avoid such problems by declaring individual variables with %TYPE qualifiers, and declaring records to hold query results with %ROWTYPE qualifiers.
  • Handle named exceptions whenever possible, instead of using WHEN OTHERS in exception handlers. Learn the names and causes of the predefined exceptions. If your database operations might cause particular ORA- errors, associate names with these errors so you can write handlers for them. (You will learn how to do that later in this chapter.)
  • Write out debugging information in your exception handlers. You might store such information in a separate table. If so, do it by making a call to a procedure declared with the PRAGMA AUTONOMOUS_TRANSACTION, so that you can commit your debugging information, even if you roll back the work that the main procedure was doing.
  • Carefully consider whether each exception handler should commit the transaction, roll it back, or let it continue. Remember, no matter how severe the error is, you want to leave the database in a consistent state and avoid storing any bad data.

Advantages of PL/SQL Exceptions

Using exceptions for error handling has several advantages. With exceptions, you can reliably handle potential errors from many statements with a single exception handler:
Example  Managing Multiple Errors With a Single Exception Handler
   emp_column       VARCHAR2(30) := 'last_name';
   table_name       VARCHAR2(30) := 'emp';
   temp_var         VARCHAR2(30);
  temp_var := emp_column;
-- processing here
  temp_var := table_name;
-- processing here
   WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors
     DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp_var);

Instead of checking for an error at every point it might occur, just add an exception handler to your PL/SQL block. If the exception is ever raised in that block (or any sub-block), you can be sure it will be handled.
Sometimes the error is not immediately obvious, and could not be detected until later when you perform calculations using bad data. Again, a single exception handler can trap all division-by-zero errors, bad array subscripts, and so on.
If you need to check for errors at a specific spot, you can enclose a single statement or a group of statements inside its own BEGIN-END block with its own exception handler. You can make the checking as general or as precise as you like.
Isolating error-handling routines makes the rest of the program easier to read and understand.

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).
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.
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 ). 
DescriptionHow to Get It
The error code. This code is useful when you need to look up generic information about what might cause such a problem.SQLCODE
Note: You cannot call this function inside a SQL statement.
The error message. This text often contains application-specific data such as the name of the constraint or the column associated with the problem.SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK
Note: You cannot call SQLERRM inside a SQL statement.
The line on which the error occurred. This capability was added in Oracle Database 10g Release 2 and is enormously helpful in tracking down the cause of errors.DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
The execution call stack. This answers the question “How did I get here?” and shows you the path through your code to the point at which DBMS_UTILITY.FORMAT_CALL_STACK is called.DBMS_UTILITY.FORMAT_CALL_STACK

Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT

To handle error conditions (typically ORA- messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.
You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax

PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

where exception_name is the name of a previously declared exception and the number is a negative value corresponding to an ORA- error number. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in Example:

   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
   NULL; -- Some operation that causes an ORA-00060 error
   WHEN deadlock_detected THEN
      NULL; -- handle the error

Raising Exception

You can, however, raise exceptions in your own code. Why would you want to do this? Because not every error in an application is due to a failure of internal processing in the Oracle Database instance. It is also possible that a certain data condition constitutes an error in your application, in which case you need to stop the processing of your algorithms and, quite likely, notify the user that something is wrong.
PL/SQL offers two mechanisms for raising an exception: 

  • The RAISE statement
  • The RAISE_APPLICATION_ERROR built-in procedure

The RAISE statement
You can use the RAISE statement to raise a user-defined exception or an Oracle Database predefined exception. In the following example, I have decided that if the user has supplied a NULL value for the department ID, I will raise the VALUE_ERROR exception: 
process_department (
   department_id_in IN INTEGER)
   IF department_id_in IS NULL
   END IF;
You can also use RAISE to reraise an exception from within the exception section.
If you need to pass an application-specific message back to your users when an error occurs, you should call the RAISE_APPLICATION_ERROR built-in procedure. This procedure accepts an integer (your error code), whose value must be between -20,999 and -20,000, and a string (your error message).
When this procedure is run, execution of the current PL/SQL block halts immediately and an exception (whose error code and message are set from the values passed to RAISE_APPLICATION_ERROR  is raised. Subsequent calls to SQLCODE and SQLERRM will return these values.
Here is an example of using RAISE_APPLICATION_ERROR  An employee must be at least 18 years old. If the date of birth is more recent, raise an error so that the INSERT or UPDATE is halted, and pass back a message to the user: 
validate_employee (
birthdate_in IN DATE)
IF birthdate_in >
, 'Employee must be at least
18 years old.');

Scope Rules for PL/SQL Exceptions

You cannot declare an exception twice in the same block. You can, however, declare the same exception in two different blocks.
Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block.
If you redeclare a global exception in a sub-block, the local declaration prevails. The sub-block cannot reference the global exception, unless the exception is declared in a labeled block and you qualify its name with the block label:

Example Scope of PL/SQL Exceptions
   past_due EXCEPTION;
   acct_num NUMBER;
   DECLARE  ---------- sub-block begins
      past_due EXCEPTION;  -- this declaration prevails
      acct_num NUMBER;
     due_date DATE := SYSDATE - 1;
     todays_date DATE := SYSDATE;
      IF due_date < todays_date THEN
         RAISE past_due;  -- this is not handled
      END IF;
   END;  ------------- sub-block ends
  WHEN past_due THEN  -- does not handle raised exception
    DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
    DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');

Redeclaring Predefined Exceptions

Remember, PL/SQL declares predefined exceptions globally in package STANDARD, so you need not declare them yourself. Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration. For example, if you declare an exception named invalid_number and then PL/SQL raises the predefined exception INVALID_NUMBER internally, a handler written for INVALID_NUMBER will not catch the internal exception. In such cases, you must use dot notation to specify the predefined exception, as follows:

    -- handle the error

Continuing after an Exception Is Raised

An exception handler lets you recover from an otherwise fatal error before exiting a block. But when the handler completes, the block is terminated. You cannot return to the current block from an exception handler. In the following example, if the SELECT INTO statement raises ZERO_DIVIDE, you cannot resume with the INSERT statement:
CREATE TABLE employees_temp AS 
  SELECT employee_id, salary, commission_pct FROM employees;

  sal_calc NUMBER(8,2);
  INSERT INTO employees_temp VALUES (301, 2500, 0);
  SELECT salary / commission_pct INTO sal_calc FROM employees_temp
    WHERE employee_id = 301;
  INSERT INTO employees_temp VALUES (302, sal_calc/100, .1);

You can still handle an exception for a statement, then continue with the next statement. Place the statement in its own sub-block with its own exception handlers. If an error occurs in the sub-block, a local handler can catch the exception. When the sub-block ends, the enclosing block continues to execute at the point where the sub-block ends, as shown in Example.
Example  Continuing After an Exception
  sal_calc NUMBER(8,2);
  INSERT INTO employees_temp VALUES (303, 2500, 0);
  BEGIN -- sub-block begins
    SELECT salary / commission_pct INTO sal_calc FROM employees_temp
      WHERE employee_id = 301;
        sal_calc := 2500;
  END; -- sub-block ends
  INSERT INTO employees_temp VALUES (304, sal_calc/100, .1);

In this example, if the SELECT INTO statement raises a ZERO_DIVIDE exception, the local handler catches it and sets sal_calc to 2500. Execution of the handler is complete, so the sub-block terminates, and execution continues with the INSERT statement

Retrying a Transaction

After an exception is raised, rather than abandon your transaction, you might want to retry it. The technique is:
  1. Encase the transaction in a sub-block.
  2. Place the sub-block inside a loop that repeats the transaction.
  3. Before starting the transaction, mark a savepoint. If the transaction succeeds, commit, then exit from the loop. If the transaction fails, control transfers to the exception handler, where you roll back to the savepoint undoing any changes, then try to fix the problem.
In Example 10-13, the INSERT statement might raise an exception because of a duplicate value in a unique column. In that case, we change the value that needs to be unique and continue with the next loop iteration. If the INSERT succeeds, we exit from the loop immediately. With this technique, you should use aFOR or WHILE loop to limit the number of attempts.
Example 10-13 Retrying a Transaction After an Exception
CREATE TABLE results ( res_name VARCHAR(20), res_answer VARCHAR2(3) );
CREATE UNIQUE INDEX res_name_ix ON results (res_name);

   name     VARCHAR2(20) := 'SMYTHE';
   answer   VARCHAR2(3) := 'NO';
   suffix   NUMBER := 1;
   FOR i IN 1..5 LOOP  -- try 5 times
      BEGIN  -- sub-block begins
         SAVEPOINT start_transaction;  -- mark a savepoint
         /* Remove rows from a table of survey results. */
         DELETE FROM results WHERE res_answer = 'NO';
         /* Add a survey respondent's name and answers. */
         INSERT INTO results VALUES (name, answer);
 -- raises DUP_VAL_ON_INDEX if two respondents have the same name
            ROLLBACK TO start_transaction;  -- undo changes
            suffix := suffix + 1;           -- try to fix problem
            name := name || TO_CHAR(suffix);
      END;  -- sub-block ends

Re-raising exceptions

You could simply record information about an error and then not reraise the exception. The problem with this approach is that your application has “swallowed up” an error. The user (or the script that is being run) will not know that there was a problem. In some scenarios, that may be OK, but they are very rare. In almost every situation when an error occurs, you really do want to make sure that the person or the job running the code that raised the error is informed.
Oracle Database makes it easy to do this with the RAISE statement. If you use RAISE in an executable section, you must specify the exception you are raising, as in  

But inside an exception handler, you can also use RAISE without any exception, as in 

In this form, Oracle Database will reraise the current exception and propagate it out of the exception section to the enclosing block.

Note that if you try to use RAISE outside of an exception section, Oracle Database will raise a compile-time error: 
PLS-00367: a RAISE statement with no exception name must be inside an exception handler

Pre-defined Exceptions

PL/SQL provides many pre-defined exceptions which are executed when any database rule is violated by a program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The following table lists few of the important pre-defined exceptions:
ExceptionOracle ErrorSQLCODEDescription
ACCESS_INTO_NULL06530-6530It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND06592-6592It is raised when none of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL06531-6531It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX00001-1It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR01001-1001It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER01722-1722It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED01017-1017It is raised when s program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND01403+100It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON01012-1012It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR06501-6501It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH06504-6504It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL30625-30625It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR06500-6500It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS01422-1422It is raised when s SELECT INTO statement returns more than one row.
VALUE_ERROR06502-6502It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.
ZERO_DIVIDE014761476It is raised when an attempt is made to divide a number by zero.


Did you know that that you can generate cash by locking special pages of your blog or site?
Simply open an account with AdWorkMedia and use their content locking tool.


If you need your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (no matter why you broke up) you must watch this video
right away...

(VIDEO) Text Your Ex Back?


Quantum Binary Signals

Get professional trading signals delivered to your cell phone daily.

Start following our trades right now and gain up to 270% a day.


You Might Also Like

Related Posts with Thumbnails