Login

Username:

Password: 

Join us Now |  Forgot Password? | Forgot UserName?

PL/SQL

Learn step by step



Exception Handling

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly.

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 message consists three types

  • Type of Exception
  • An Error Code
  • 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;



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

Output:

SQL>@TutOn_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
CURSOR_ALREADY_OPEN ORA-06511 Exception raised when you open a cursor that is already opened.
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.
INVALID_CURSOR ORA-01001 Exception raised when you perform operation on cursor and cursor is not really opened.
DUP_VAL_ON_INDEX ORA-00001 Exception raised when you store duplicate value in unique constraint column.
INVALID_NUMBER ORA-01722 Exception raised when you try to explicitly conversion from string to a number fail.
ACCESS_INTO_NULL ORA-06530 Exception raised when assign uninitialized (NULL) object.
NO_DATA_FOUND ORA-01403 Exception raised when SELECT ... INTO statement doesn't fetch any row from a database table.
LOGIN_DENIED ORA-01017 Exception raised when log in into oracle with wrong username or password.
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.
NOT_LOGGED_ON ORA-01012 Exception raised when your program try to get data from database and actually user not connected to Oracle.
SYS_INVALID_ROWID ORA-01410 Exception raised when you try to explicitly conversion from string character string to a universal rowid (uid) fail.
ZERO_DIVIDE ORA-01476 Exception raised when you program try to attempt divide by zero number.
TIMEOUT_ON_RESOURCE ORA-00051 Exception raised when database is locked or ORACLE is waiting for a resource.
VALUE_ERROR ORA-06502 Exception raised when arithmetic, conversion, defined size constraint error occurs.
TOO_MANY_ROWS ORA-01422 Exception raised when SELECT ... INTO statement returns more than one row.
PL/SQL User Defined Exception

Apart from system exceptions we can explicitly define exceptions based on business rules. These are known as user-defined exceptions.

Steps to be followed to use user-defined exceptions:

  • They should be handled by referencing the user-defined exception name in the exception section.
  • They should be explicitly declared in the declaration section.
  • They should be explicitly raised in the Execution Section.

How to Define Exception

Declare exception
We must declare user define exception name in DECALARE block.

user_define_exception_name EXCEPTION;

RAISE exception
RAISE statement to raised defined exception name and control transfer to a EXCEPTION block.

RAISE user_define_exception_name;

Implement exception condition
In PL/SQL EXCEPTION block add WHEN condition to implement user define action.

WHEN user_define_exception_name THEN
User defined statement (action) will be taken;
Syntax
DECLARE
user_define_exception_name EXCEPTION;
BEGIN
statement(s);
IF condition THEN
RAISE user_define_exception_name;
END IF;
EXCEPTION
WHEN user_define_exception_name THEN
User defined statement (action) will be taken;
END;



Example: TutOn.sql
SQL>edit TutOn
DECLARE
myex EXCEPTION;
i NUMBER;
BEGIN
FOR i IN (SELECT * FROM enum) LOOP
IF i.eno = 3 THEN
RAISE myex;
END IF;
END LOOP;
EXCEPTION
WHEN myex THEN
dbms_output.put.line('Employee number already exist in enum table.');
END;

Output

SQL>@TutOn
Employee number already exist in enum table.

PL/SQL procedure successfully operation.
User Named Exception

PL/SQL user named exception. We can define our own error message and error number using Pragma EXCEPTION_INIT or RAISE_APPLICATION_ERROR function.

PL/SQL RAISE_APPLICATION_ERROR

In PL/SQL RAISE_APPLICATION_ERROR function use to assign exception name and exception error code. Define RAISE_APPLICATION_ERROR function syntax,

raise_application_error(error_number, error_message);



Example: TutOn_raiseAppError.sql
SQL>edit TutOn_raiseAppError
DECLARE
myex EXCEPTION;
n NUMBER := &n;
BEGIN
FOR i IN 1..n LOOP
dbms_output.put.line(i);
IF i=n THEN
RAISE myex;
END IF;
END LOOP;
EXCEPTION
WHEN myex THEN
RAISE_APPLICATION_ERROR(-20015, 'loop finish');
END;

Output

SQL>@TutOn_raiseAppError
n number &n= 5
1
2
3
4
5
ORA-20015: loop finish

PL/SQL procedure successfully operation.

When RAISE_APPLICATION_ERROR execute it's return error message and error code looking same as oracle built-in error.

PL/SQL pragma EXCEPTION_INIT

Pragma is a keyword directive to execute proceed at compile time. pragma EXCEPTION_INIT function take this two argument

  • exception_name
  • error_number

We can define pragrma EXCEPTION_INIT in DECLARE BLOCK on your program.

PRAGMA EXCEPTION_INIT(exception_name, -error_number);

exception_name and error_number define on yourself, where exception_name is character string up to 2048 bytes suppot and error_number is a negative integer range from -20000 to -20999.

Syntax
DECLARE
user_define_exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT(user_define_exception_name,-error_number);
BEGIN
statement(s);
IF condition THEN
RAISE user_define_exception_name;
END IF;
EXCEPTION
WHEN user_define_exception_name THEN
User defined statement (action) will be taken;
END;



Example: TutOn_user-named.sql
SQL>edit TutOn_user-named DECLARE
myex EXCEPTION;
PRAGMA EXCEPTION_INIT(myex,-20015);
n NUMBER := &n;
BEGIN
FOR i IN 1..n LOOP
dbms_output.put.line(i);
IF i=n THEN
RAISE myex;
END IF;
END LOOP;
EXCEPTION
WHEN myex THEN
dbms_output.put.line('loop finish');
END;

Output

SQL>@TutOn_user-named n number &n= 5
1
2
3
4
5
loop finish

PL/SQL procedure successfully operation.


Related Videos