Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL


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

PL/SQL Exceptions

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;

Example Code

builtin_exp.sql
SQL>edit buitin_exp
DECLARE
	temp enum%rowtype;
BEGIN
	SELECT * INTO temp FROM enum
		WHERE eno=3;
EXCEPTION
	WHEN no_data_found THEN
		dbms_output.put_line("Table haven't data");
END;
/

Example Result

SQL>@buitin_exp
Table haven't data

PL/SQL procedure successfully operation.

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.