Exception

From Oracle FAQ
Jump to: navigation, search

An exception is a section in a PL/SQL program that captures and processes runtime errors.

[edit] Predefined exceptions

Oracle provides some predefined exceptions that can be used, including: NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, VALUE_ERROR, ZERO_DIVIDE, INVALID_CURSOR, NOT_LOGGED_ON, etc.

Code example:

DECLARE
  d VARCHAR2(1);
BEGIN
  SELECT dummy INTO d FROM dual WHERE 1=2;
EXCEPTION
  WHEN no_data_found THEN 
     DBMS_OUTPUT.PUT_LINE('ERROR: No data!');
END;

[edit] Custom exceptions

Here is an example of how to define a custom expection:

DECLARE
  view_doesnot_exist EXCEPTION;
  PRAGMA             EXCEPTION_INIT(view_doesnot_exist, -942);
BEGIN
  ...
EXCEPTION
  WHEN view_doesnot_exist THEN null;
END;

The -924 in the above example links the name view_doesnot_exist to ORA-00924: table or view does not exist.

Custom exceptions can be declared in a separate package to make them "global" and reusable. Here is an example:

CREATE OR REPLACE PACKAGE my_exceptions
AS
  insert_null_into_notnull EXCEPTION;
  PRAGMA EXCEPTION_INIT(insert_null_into_notnull, -1400);

  update_null_to_notnull EXCEPTION;
  PRAGMA EXCEPTION_INIT(update_null_to_notnull, -1407);
END my_exceptions;
/
CREATE OR REPLACE PROCEDURE use_an_exception AS
BEGIN
  -- application specific code ...
  NULL;
EXCEPTION
  WHEN my_exceptions.insert_null_into_notnull THEN
     -- application specific handling for ORA-01400: cannot insert NULL into (%s)
     RAISE;
END;
/

[edit] Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #