PL/SQL Exceptions

In PL/SQL, EXCEPTION is a general errors that fire during program execution, either implicitly by TimesTen or explicitly by your program. To handle any kind of errors or EXCEPTION by trapping it with a handler is called EXCEPTION handling.

The PL/SQL allow programmers to catch such exception using EXCEPTION block in the program and take appropriate action against the exception. The PL/SQL provides you with a flexible and powerful way to handle such exceptions.

There are two types of exceptions in PL/SQL:

System-defined Exceptions
User-defined Exceptions

For example, if your SELECT statement returns multiple rows, TimesTen returns an error (exception) at runtime. As the following example shows, you would see TimesTen error 8507, then the associated ORA error message. (ORA messages, originally defined for Oracle Database, are similarly implemented by TimesTen.)

PL/SQL Exception Handling Syntax

Here you can list down as many exceptions as you can handle. To handle an exception, use the following syntax.

Syntax

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)> 
EXCEPTION 
   <exception handling goes here > 
   WHEN exception1 THEN  
      exception1-handling-statements  
   WHEN exception2  THEN  
      exception2-handling-statements  
   ........ 
   WHEN others THEN 
      exception2-handling-statements 
END;

Where:

exception1, exception2...: The exceptions name.

PL/SQL Exception Handling Example

To handle an EXCEPTION in PL/SQL, use the following syntax:

Let's take an example to show how to use exception handling. In this example, we are using the already created tblCustomerLoan table.

Syntax

DECLARE 
   c_id customers.id%type := 18; 
   c_fname user.firstName%type; 
   c_lname user.lastName%type;
   c_addr user.address%type; 
   c_aadh user.aadharNo%type; 
BEGIN 
   SELECT firstname, lastName, address, aadharNo INTO c_fname, c_lname, c_addr, c_aadh; 
   FROM tblCustomerLoan 
   WHERE pk = c_id;
   DBMS_OUTPUT.PUT_LINE(c_fname || ' ' || c_lname || ' ' || c_addr || ' ' || c_aadh); 

EXCEPTION 
   WHEN no_data_found THEN 
      DBMS_OUTPUT.PUT_LINE('No such customer!'); 
   WHEN others THEN 
      DBMS_OUTPUT.PUT_LINE('Error!');   
END;

Note:

The above example, demonstrate that the program raises the run-time exception NO_DATA_FOUND because there is no customer with pk value 18 is exists in database table, which is catch in the EXCEPTION block.

PL/SQL Raising Exceptions Example

Usually exceptions are raised by the database server automatically whenever any error occurs. The PL/SQL allows you to raise exception explicitly from your programe by using the command RAISE. To raise an standard exception explicitly in PL/SQL use the following syntax:

Syntax

DECLARE 
   exception_name EXCEPTION; 
BEGIN 
   IF condition THEN 
      RAISE exception_name; 
   END IF; 
EXCEPTION 
   WHEN exception_name THEN 
   statement; 
END;

Where:

exception_name: The exceptions name.

RAISE: To raise the exception.

PL/SQL User-defined Exceptions Syntax

In PL/SQL you can define your own exceptions where you required in the program. In order to use user-defined exception, first you declared and then raised explicitly using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

Syntax

DECLARE 
   my-exception EXCEPTION;

Where:

my-exception: Exceptions that you want ro raise.

PL/SQL Raising Exceptions Example

Example

DECLARE 
   c_id customers.id%type := 0; 
   c_fname user.firstName%type; 
   c_lname user.lastName%type;
   c_addr user.address%type; 
   c_aadh user.aadharNo%type;
   invalid_id_excp  EXCEPTION; 
BEGIN 
	IF c_id <= 0 THEN 
      RAISE invalid_id_excp; 
   ELSE 
	   SELECT firstname, lastName, address, aadharNo INTO c_fname, c_lname, c_addr, c_aadh; 
	   FROM tblCustomerLoan 
	   WHERE pk = c_id;
	   DBMS_OUTPUT.PUT_LINE(c_fname || ' ' || c_lname || ' ' || c_addr || ' ' || c_aadh); 
   END IF; 
EXCEPTION 
   WHEN invalid_id_excp THEN 
      DBMS_OUTPUT.PUT_LINE('ID must be greater than zero!'); 
   WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('No such customer!'); 
   WHEN others THEN 
      DBMS_OUTPUT.PUT_LINE('Error!');    
END;

PL/SQL Pre-defined Exceptions

PL/SQL offers some pre-defined exceptions, which are executed when any error occur in the program. For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. Below are the list of important pre-defined exceptions:

Exception	Oracle Error	SQL Code	Description
ACCESS_INTO_NULL	06530	-6530	It is raised when a NULL object is automatically assigned a value.
CASE_NOT_FOUND	06592	-6592	It is raised when none of the choices in the ?WHEN? clauses of a CASE statement is selected, and there is no else clause.
COLLECTION_IS_NULL	06531	-6531	It is raised when 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.
DUP_VAL_ON_INDEX	00001	-1	It is raised when duplicate values are attempted to be stored in a column with unique index.
INVALID_CURSOR	01001	-1001	It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER	01722	-1722	It is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
LOGIN_DENIED	01017	-1017	It is raised when s program attempts to log on to the database with an invalid username or password.
NO_DATA_FOUND	01403	+100	It is raised when a select into statement returns no rows.
NOT_LOGGED_ON	01012	-1012	It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR	06501	-6501	It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH	06504	-6504	It is raised when a cursor fetches value in a variable having incompatible data type.
SELF_IS_NULL	30625	-30625	It is raised when a member method is invoked, but the instance of the object type was not initialized.
STORAGE_ERROR	06500	-6500	It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS	01422	-1422	It is raised when a SELECT INTO statement returns more than one row.
VALUE_ERROR	06502	-6502	It is raised when an arithmetic, conversion, truncation, or size-constraint error occurs.
ZERO_DIVIDE	01476	1476	It is raised when an attempt is made to divide a number by zero.