Summary of Predefined PL/SQL Exceptions

Summary of Predefined PL/SQL Exceptions

An internal exception is raised automatically if your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. In PL/SQL common Oracle errors are predefined as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. To handle unexpected Oracle errors, you can use the OTHERS handler.
PL/SQL declares predefined exceptions globally in package STANDARD so you do not need to declare them. You can write handlers for predefined exceptions using the predefined names. Table 4-1 lists some of the predefined exceptions.

Table 4-1 Predefined PL/SQL Exceptions

Exception	Description
ACCESS_INTO_NULL	A program attempts to assign values to the attributes of an uninitialized object
CASE_NOT_FOUND	None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL	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.
CURSOR_ALREADY_OPEN	A program attempts to open a cursor that is already open. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX	A program attempts to store duplicate values in a column that is constrained by a unique index.
INVALID_CURSOR	A program attempts a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER	In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
LOGIN_DENIED	A program attempts to log on to Oracle Database XE with a user name or password that is not valid.
NO_DATA_FOUND	A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. Because this exception is used internally by some SQL functions to signal completion, do not rely on this exception being propagated if you raise it within a function that is called as part of a query.
NOT_LOGGED_ON	A program issues a database call without being connected to Oracle Database XE.
ROWTYPE_MISMATCH	The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. When an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SUBSCRIPT_BEYOND_COUNT	A program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT	A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
TOO_MANY_ROWS	A SELECT INTO statement returns more than one row.
VALUE_ERROR	An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL cancels the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
ZERO_DIVIDE	A program attempts to divide a number by zero.

Using the Exception Handler

Using exceptions for error handling has several advantages. With exceptions, you can reliably handle potential errors from many statements with a single exception handler, as shown in Example 4-37.

Example 4-37 Managing Multiple Errors With a Single PL/SQL Exception Handler

DECLARE  -- declare variables
   emp_column       VARCHAR2(30) := 'last_name';
   table_name       VARCHAR2(30) := 'emp';  -- set value to raise error
   temp_var         VARCHAR2(30);
BEGIN
  temp_var := emp_column;
  SELECT COLUMN_NAME INTO temp_var FROM USER_TAB_COLS 
    WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = UPPER(emp_column);
-- processing here
  temp_var := table_name;
  SELECT OBJECT_NAME INTO temp_var FROM USER_OBJECTS
    WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE';
-- processing here
EXCEPTION
   WHEN NO_DATA_FOUND THEN  -- catches all 'no data found' errors
     DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp_var);
END;
/

Declaring PL/SQL Exceptions

Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the EXCEPTION keyword. In Example 4-38, you declare an exception named past_due that is raised when the due_date is less than the today's date.
Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.

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 subblocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a subblock.
If you redeclare a global exception in a subblock, the local declaration prevails. The subblock cannot reference the global exception, unless the exception is declared in a labeled block and you qualify its name with the block label, for example:
block_label.exception_name
Example 4-38 shows the scope rules.

Example 4-38 Determining the Scope of PL/SQL Exceptions

DECLARE
   past_due EXCEPTION;
   acct_num NUMBER;
BEGIN
   DECLARE  ---------- subblock begins
     past_due EXCEPTION;  -- this declaration prevails
     acct_num NUMBER;
     due_date DATE := SYSDATE - 1; -- set on purpose to raise exception
     todays_date DATE := SYSDATE;
   BEGIN
      IF due_date < todays_date THEN
         RAISE past_due;  -- this is not handled
      END IF;
   END;  ------------- subblock ends
EXCEPTION
  WHEN past_due THEN  -- does not handle raised exception
    DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/

The enclosing block does not handle the raised exception because the declaration of past_due in the subblock prevails. Although they share the same name, the two past_due exceptions are different, just as the two acct_num variables share the same name but are different variables. Thus, the RAISE statement and the WHEN clause refer to different exceptions. To have the enclosing block handle the raised exception, you must remove its declaration from the subblock or define an OTHERS handler.

Continuing After an Exception Is Raised

By default, you put an exception handler at the end of a subprogram to handle exceptions that are raised anywhere inside the subprogram. To continue execution from the spot where an exception occurred, enclose the code that might raise an exception inside another BEGIN-END block with its own exception handler. For example, put separate BEGIN-END blocks around groups of SQL statements that might raise NO_DATA_FOUND, or around arithmetic operations that might raise DIVIDE_BY_ZERO. By putting a BEGIN-END block with an exception handler inside of a loop, you can continue executing the loop if some loop iterations raise exceptions.
You can still handle an exception for a statement, then continue with the next statement. Place the statement in its own subblock with its own exception handlers. If an error occurs in the subblock, a local handler can catch the exception. When the subblock ends, the enclosing block continues to execute at the point where the subblock ends, as shown in Example 4-39.

Example 4-39 Continuing After an Exception in PL/SQL

-- create a temporary table for this example
CREATE TABLE employees_temp AS 
  SELECT employee_id, salary, commission_pct FROM employees;

DECLARE
  sal_calc NUMBER(8,2);
BEGIN
  INSERT INTO employees_temp VALUES (303, 2500, 0);
  BEGIN -- subblock begins
    SELECT salary / commission_pct INTO sal_calc FROM employees_temp
      WHERE employee_id = 303;
    EXCEPTION
      WHEN ZERO_DIVIDE THEN
        sal_calc := 2500;
  END; -- subblock ends
  INSERT INTO employees_temp VALUES (304, sal_calc/100, .1);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    NULL;
END;
/
-- view the results
SELECT * FROM employees_temp WHERE employee_id = 303 OR employee_id = 304;
-- drop the temporary table
DROP TABLE employees_temp;