PL/SQL Exceptions

PL/SQL exceptions are predefined and raised automatically into oracle engine when any error occur during a program.

Each and every error has defined a unique number and message. When warning/error occur in program it's called an exception to contains information about the error

In PL/SQL built in exceptions or you make user define exception. Examples of built-in type (internally) defined exceptions division by zero, out of memory. Some common built-in exceptions have predefined names such as ZERO_DIVIDE and STORAGE_ERROR.

Normally when exception is fire, execution stops and control transfers to the exception-handling part of your PL/SQL block. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which are also raise predefined exceptions.

PL/SQL exceptions consist following three,

    1.   Exception Type

    2.   Error Code

    3.   Error Message

Syntax

DECLARE

      declaration statement(s);  

BEGIN 

      statement(s); 

EXCEPTION

      WHEN built-in_exception_name_1 THEN

              User defined statement (action) will be taken;

      WHEN built-in_exception_name_2 THEN

              User defined statement (action) will be taken;  

END;

PL/SQL built in exceptions


Following are built in type exception,


 


  

Exception

  

Error Code

  

Description

 

  

ACCESS_INTO_NULL

  

ORA-06530

  

Exception raised when assign uninitialized (NULL)
  object.

 

  

CASE_NOT_FOUND

  

ORA-06592

  

Exception raised when no any choice case found in
  CASE statement as well as no ELSE clause in CASE statement.

 

  

CURSOR_ALREADY_OPEN

  

ORA-06511

  

Exception raised when you open a cursor that is
  already opened.

 

  

DUP_VAL_ON_INDEX

  

ORA-00001

  

Exception raised when you store duplicate value in
  unique constraint column.

 

  

INVALID_CURSOR

  

ORA-01001

  

Exception raised when you perform operation on
  cursor and cursor is not really opened.

 

  

INVALID_NUMBER

  

ORA-01722

  

Exception raised when you try to explicitly
  conversion from string to a number fail.

 

  

LOGIN_DENIED

  

ORA-01017

  

Exception raised when log in into oracle with wrong
  username or password.

 

  

NO_DATA_FOUND

  

ORA-01403

  

Exception raised when SELECT ... INTO statement
  doesn't fetch any row from a database table.

 

  

NOT_LOGGED_ON

  

ORA-01012

  

Exception raised when your program try to get data
  from database and actually user not connected to Oracle.

 

  

PROGRAM_ERROR

  

ORA-06501

  

Exception raised when your program is error prone
  (internal error).

 

  

STORAGE_ERROR

  

ORA-06500

  

Exception raised when PL/SQL program runs out of
  memory or may be memory is dumped/corrupted.

 

  

SYS_INVALID_ROWID

  

ORA-01410

  

Exception raised when you try to explicitly
  conversion from string character string to a universal rowid (uid) fail.

 

  

TIMEOUT_ON_RESOURCE

  

ORA-00051

  

Exception raised when database is locked or ORACLE
  is waiting for a resource.

 

  

TOO_MANY_ROWS

  

ORA-01422

  

Exception raised when SELECT ... INTO statement
  returns more than one row.

 

  

VALUE_ERROR

  

ORA-06502

  

Exception raised when arithmetic, conversion,
  defined size constraint error occurs.

 

  

ZERO_DIVIDE

  

ORA-01476

  

Exception raised when you program try to attempt
  divide by zero number.

 




Important:- What does the PL/SQL raise_application_error code do?

Answer:  The raise_application_error  is
actually a procedure defined by Oracle that allows the developer to raise an
exception and associate an error number and message with the procedure. This
allows the application to raise application errors rather than just Oracle
errors. Error numbers are defined between -20,000 and -20,999.

Oracle provides
the raise_application_error procedure to allow you to raise
custom error numbers within your applications. You can generate errors and
their associated text starting with -20000 and proceeding through -20999 (a
grand total of 1,000 error numbers that you can use). Below we illustrate the
use of the raise_application_error procedure. 


Using the raise_application_error procedure:


DECLARE

Balance integer := 24;



BEGIN

IF (nBalance <= 100) THEN

Raise_Application_Error (-20343,
'The balance is too low.');

END IF;

END;

In this example, error number -20343 is raised if the value of nBalance isn't
greater than 100, yielding a message that looks like this: 

ORA-20343: The balance is too low.