11 PL/SQL Error Handling

Advantages of Exceptions

With exceptions, you can reliably handle potential errors from many statements with a single exception handler, as in Example 11-5.

DECLARE
  emp_column  VARCHAR2(30) := 'last_name';
  table_name  VARCHAR2(30) := 'emp';
  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);

  temp_var := table_name;

  SELECT OBJECT_NAME INTO temp_var
  FROM USER_OBJECTS
  WHERE OBJECT_NAME = UPPER(table_name)
  AND OBJECT_TYPE = 'TABLE';

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp_var);
END;
/

Instead of checking for an error at every point where it might occur, add an exception handler to your PL/SQL block. If the exception is ever raised in that block (including inside a sub-block), it is handled.

Sometimes the error is not immediately obvious, and cannot 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 must 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 exception handlers makes the rest of the program easier to read and understand.

Guidelines for Avoiding and Handling 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 errors can occur.

  Errors are especially likely during arithmetic calculations, string manipulation, and database operations. Errors can also occur at other times, for example if a hardware failure with disk storage or memory causes a problem that has nothing to do with your code; but your code still must take corrective action.

• Add error-checking code whenever bad input data can cause an error.

  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.

  Test your code with different combinations of bad data to see what potential errors arise.

• 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-n errors, associate names with these errors so you can write handlers for them.

• Write out debugging information in your exception handlers.

  You might store such information in a separate table. If so, do it by invoking a subprogram declared with the PRAGMA AUTONOMOUS_TRANSACTION, so that you can commit your debugging information, even if you roll back the work that the main subprogram was doing.

• Carefully consider whether each exception handler should commit the transaction, roll it back, or let it continue.

  No matter how severe the error is, you want to leave the database in a consistent state and avoid storing any bad data.

Exception Declarations

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 keyword EXCEPTION. For example:

DECLARE
  past_due  EXCEPTION;
BEGIN
  NULL;
END;
/

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.

Exception Scope

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 block_label.exception_name.

Example 11-6 shows the scope rules.

DECLARE
  past_due  EXCEPTION;
  acct_num  NUMBER;
BEGIN
  DECLARE  -- sub-block begins
    past_due EXCEPTION;  -- this declaration prevails
    acct_num NUMBER;
    due_date DATE := SYSDATE - 1;
    todays_date DATE := SYSDATE;
  BEGIN
    IF due_date < todays_date THEN
      RAISE past_due;  -- this is not handled
    END IF;
  END;  -- sub-block ends
EXCEPTION
  -- Does not handle raised exception
  WHEN past_due THEN
    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;
/

Could not recognize PAST_DUE_EXCEPTION in this scope.

The enclosing block does not handle the raised exception because the declaration of past_due in the sub-block prevails. Though 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 sub-block or define an OTHERS handler.

Associating Exceptions with Numbers

To handle an error condition (typically an ORA-n message) that has 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 Database 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 this 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-n error. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in Example 11-7.

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

User-Defined Error Messages

The RAISE_APPLICATION_ERROR procedure lets you issue user-defined ORA-n error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

To invoke RAISE_APPLICATION_ERROR, use this syntax:

RAISE_APPLICATION_ERROR (error_number, message[, {TRUE | FALSE}]);

where error_number is a negative integer in the range -20000..-20999 and message is a character string of at most 2048 bytes. If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors.

An application can invoke RAISE_APPLICATION_ERROR only from an running stored subprogram or method. When invoked, RAISE_APPLICATION_ERROR ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle Database error.

Example 11-8 invokes RAISE_APPLICATION_ERROR if the current schema owns fewer than 1000 tables.

DECLARE
  num_tables NUMBER;
BEGIN
  SELECT COUNT(*) INTO num_tables FROM USER_TABLES;
  IF num_tables < 1000 THEN
    /* Issue your own error code (ORA-20101)
       with your own error message. You need not
       qualify RAISE_APPLICATION_ERROR with
       DBMS_STANDARD */
    RAISE_APPLICATION_ERROR
      (-20101, 'Expecting at least 1000 tables');
  ELSE
    -- Do rest of processing (for nonerror case)
    NULL;
  END IF;
END;
/

DECLARE
*
ERROR at line 1:
ORA-20101: Expecting at least 1000 tables
ORA-06512: at line 10

The invoking application gets a PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. Also, it can use the pragma EXCEPTION_INIT to map specific error numbers returned by RAISE_APPLICATION_ERROR to exceptions of its own, as this Pro*C example shows:

EXEC SQL EXECUTE
  /* Execute embedded PL/SQL block using host
     variables v_emp_id and v_amount, which were
     assigned values in the host environment. */
DECLARE
  null_salary EXCEPTION;
  /* Map error number returned by RAISE_APPLICATION_ERROR
     to user-defined exception. */
  PRAGMA EXCEPTION_INIT(null_salary, -20101);
  BEGIN
    raise_salary(:v_emp_id, :v_amount);
  EXCEPTION
    WHEN null_salary THEN
      INSERT INTO emp_audit (employee_id, ...) VALUES (:v_emp_id, ...);
  END;
END-EXEC;

This technique allows the invoking application to handle error conditions in specific exception handlers.

Redeclaring Predefined Exceptions

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 cannot catch the internal exception. In such cases, you must use dot notation to specify the predefined exception. For example:

BEGIN
  NULL;
EXCEPTION
  WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN
    NULL;  -- handle the error
END;
/

Exceptions Raised in Declarations

In declarations, faulty initialization expressions can raise exceptions, but exception handlers cannot handle them, as Example 11-13 shows.

DECLARE
  credit_limit CONSTANT NUMBER(3) := 5000;  -- Maximum value is 999
BEGIN
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    -- Cannot catch exception. This handler is never invoked.
    DBMS_OUTPUT.PUT_LINE
      ('Can''t handle an exception in a declaration.');
END;
/

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 2

Handlers in the current block cannot catch the raised exception because an exception raised in a declaration propagates immediately to the enclosing block.

Exceptions Raised in Exception Handlers

When an exception occurs in an exception handler, that same handler cannot catch the exception. An exception raised inside a handler propagates immediately to the enclosing block, which is searched to find a handler for this exception. From there on, the exception propagates normally. For example:

EXCEPTION
  WHEN INVALID_NUMBER THEN
    INSERT INTO ... -- might raise DUP_VAL_ON_INDEX
  WHEN DUP_VAL_ON_INDEX THEN -- cannot catch exception
END;

Transferring Control to or from Exception Handlers

A GOTO statement can transfer control from an exception handler into an enclosing block.

A GOTO statement cannot transfer control into an exception handler, or from an exception handler into the current block.

Error Code and Error Message Retrieval

In an exception handler, you can retrieve the error code with the built-in function SQLCODE. To retrieve the associated error message, you can use either the packaged function DBMS_UTILTY.FORMAT_ERROR_STACK or the built-in function SQLERRM.

SQLERRM returns a maximum of 512 bytes, which is the maximum length of an Oracle Database error message (including the error code, nested messages, and message inserts, such as table and column names). DBMS_UTILTY.FORMAT_ERROR_STACK returns the full error stack, up to 2000 bytes. Therefore, DBMS_UTILTY.FORMAT_ERROR_STACK is recommended over SQLERRM, except when using the FORALL statement with its SAVE EXCEPTIONS clause. With SAVE EXCEPTIONS, use SQLERRM, as in Example 12-11.

A SQL statement cannot invoke SQLCODE or SQLERRM. To use their values in a SQL statement, assign them to local variables first, as in Example 11-14.

DROP TABLE errors;
CREATE TABLE errors (
  code      NUMBER,
  message   VARCHAR2(64),
  happened  TIMESTAMP
);

DECLARE
  name    EMPLOYEES.LAST_NAME%TYPE;
  v_code  NUMBER;
  v_errm  VARCHAR2(64);
BEGIN
  SELECT last_name INTO name
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = -1;
EXCEPTION
  WHEN OTHERS THEN
    v_code := SQLCODE;
    v_errm := SUBSTR(SQLERRM, 1, 64);
    DBMS_OUTPUT.PUT_LINE
      ('Error code ' || v_code || ': ' || v_errm);
 
    /* Invoke another procedure,
       declared with PRAGMA AUTONOMOUS_TRANSACTION,
       to insert information about errors. */
 
    INSERT INTO errors (code, message, happened)
    VALUES (v_code, v_errm, SYSTIMESTAMP);
END;
/

Error code 100: ORA-01403: no data found

Continuing Execution After an Exception Is Raised

An exception handler lets you recover from an otherwise irrecoverable error before exiting a block. When the handler completes, the block terminates—you cannot return to the block from the exception handler.

In Example 11-15, if the SELECT INTO statement raises ZERO_DIVIDE, execution cannot resume with the INSERT statement.

DROP TABLE employees_temp;
CREATE TABLE employees_temp AS
  SELECT employee_id, salary, commission_pct
  FROM employees;

DECLARE
  sal_calc NUMBER(8,2);
BEGIN
  INSERT INTO employees_temp (employee_id, salary, commission_pct)
  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);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    NULL;
END;
/

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 run at the point where the sub-block ends, as shown in Example 11-16.

DECLARE
  sal_calc NUMBER(8,2);
BEGIN
  INSERT INTO employees_temp (employee_id, salary, commission_pct)
  VALUES (303, 2500, 0);
 
  BEGIN  -- sub-block begins
    SELECT (salary / commission_pct) INTO sal_calc
    FROM employees_temp
    WHERE employee_id = 301;
  EXCEPTION
    WHEN ZERO_DIVIDE THEN
      sal_calc := 2500;
  END; -- sub-block ends
 
  INSERT INTO employees_temp (employee_id, salary, commission_pct)
  VALUES (304, sal_calc/100, .1);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    NULL;
END;
/

In Example 11-16, 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.

You can also perform a sequence of DML operations where some might fail, and process the exceptions only after the entire operation is complete, as described in "Handling FORALL Exceptions".

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 11-17, the INSERT statement might raise an exception because of a duplicate value in a unique column. In that case, change the value that must be unique and continue with the next loop iteration. If the INSERT succeeds, exit from the loop immediately. With this technique, use a FOR or WHILE loop to limit the number of attempts.

DROP TABLE results;
CREATE TABLE results (
  res_name VARCHAR(20),
  res_answer VARCHAR2(3)
);
 
CREATE UNIQUE INDEX res_name_ix ON results (res_name);
 
INSERT INTO results (res_name, res_answer)
VALUES ('SMYTHE', 'YES');
 
INSERT INTO results (res_name, res_answer) 
VALUES ('JONES', 'NO');
 
DECLARE
  name    VARCHAR2(20) := 'SMYTHE';
  answer  VARCHAR2(3) := 'NO';
  suffix  NUMBER := 1;
BEGIN
  FOR i IN 1..5 LOOP  -- try 5 times
    BEGIN  -- sub-block begins
       SAVEPOINT start_transaction;
 
       -- 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  (res_name, res_answer)
       VALUES (name, answer);
       -- If two respondents have same name, raise DUP_VAL_ON_INDEX.
 
       COMMIT;
       EXIT;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        ROLLBACK TO start_transaction;  -- undo changes
        suffix := suffix + 1;           -- try to fix problem
        name := name || TO_CHAR(suffix);
    END;  -- sub-block ends
  END LOOP;
END;
/

Identifying Exception Locations With Locator Variables

Using one exception handler for a sequence of statements, such as INSERT, DELETE, or UPDATE statements, can mask the statement that caused an error. If you must know which statement failed, you can use a locator variable, as in Example 11-18.

CREATE OR REPLACE PROCEDURE loc_var AS
  stmt_no  NUMBER;
  name_    VARCHAR2(100);
BEGIN
  stmt_no := 1;  -- designates 1st SELECT statement

  SELECT table_name INTO name_
  FROM user_tables
  WHERE table_name LIKE 'ABC%';

  stmt_no := 2;  -- designates 2nd SELECT statement

  SELECT table_name INTO name_
  FROM user_tables
  WHERE table_name LIKE 'XYZ%';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Table name not found in query ' || stmt_no);
END;
/
CALL loc_var();

Table name not found in query 1
 
Call completed.