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;
Demo
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;
Demo
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);
Demo
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;
Demo
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