Exception in Oracle
PL/SQL provides a features to handle the exceptions which occurs in a PL/SQL block known as exception handling using exception handling we can test the code and avoid it from exiting abruptly It’s is called Exception handling.
When an exception occurs a messages which explain its cause is received PL/SQL exception massage consists of two parts.
Types of exception
1) System exception
2) User-defined exception
Syntax:-
EXCEPTION
When exceptionname then
--code to handle exception.1) System Exception:
==> System Exception can be further divided into two parts:
1. Named exceptions
2. Numbered exceptions
1) Named exception:
==> Particular name given to some common system exceptions is known as Named Exceptions.
==> Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.
==> Oracle has defined 15 to 20 named exception.
Some of the named exceptions are listed in below table:
| Exception | Description |
|---|---|
| INVALID_NUMBER | TO_NUMBER function failed in converting string to number |
| NO_DATA_FOUND | SELECT…INTO statement couldn’t find data |
| ZERO_DIVIDE | Divide by zero error occured |
| TOO_MANY_ROWS | SELECT_INTO statement found more than one record |
| LOGIN_DENIED | Invalid username or password found while logging |
| NOT_LOGGED_ON | Statements tried to execute without logging |
| INVALID_CURSOR | A cursor is attempted to use which is not open |
| PROGRAM_ERROR | Pl/SQL found internal problem |
| DUP_VAL_ON_INDEX | Duplicate value found in column defined as unique or primary key |
| VALUE_ERROR | Error occurred during conversion of data |
| OTHER | Stands for all other exceptions |
2) Numbered exception
==> This exception are identified by using negative signed numbered.
==> This are in build exceptions.
==> A Numbered exception is raised automatically if your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit.
==> You can use the pragma EXCEPTION_INIT to associate exception names with other Oracle error codes that you can anticipate.Within this handler, you can call the functions SQLCODE and SQLERRM to return the Oracle error code and message text. Once you know the error code, you can use it with pragma EXCEPTION_INIT and write a handler specifically for that error.
To understand named exception we design pl/sql program as shown below in notepad with .sql extension.
We are using student table as shown below:
Student Table
Here we are displaying 'name' and 'city' of student as per 'id' entered by user,using student table and if data of that student is not found we will display our own message using exception block.
Named Exception
Now, We run the file using CMD to get output.
First of all open the CMD and go to drive where you save the file using following command.
Now type the "SQLPLUS" and Write username and password to open sql in the folder where you saved.
Now write "SET SERVEROUTPUT ON" to display the output without this output of program is unseen.
To execute the program you need to write @filename.sql
If the data of student is available in the table the output will be as shown below.
If the data of student is not available in the table the output will be as shown below.
2) User-defined exception:
==> User also can define their own exception are known as user define exception.
==> These exceptions are used to validate business rules like balance for any account should not be negative value.
Syntax
Syntax for exception declaration:
exceptionName EXCEPTION;
syntax for exception raised:
RAISE exceptionname;To understand User-defined exception we design pl/sql program as shown below in notepad with .sql extension.
User-defined exception
After this we follow same steps as we follow above to run program.
If the data of student is available in the table the output will be as shown below.
If the data of student is not available in the table the output will be as shown below.
If the ID of student is less than zero then output will be as shown below.
I hope this article will help you