Exception

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

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;

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;
/

Also see

• PL/SQL

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	#