What are Implicit exception of PL SQL? | Implicit exception example

I have given the details about the PL SQL block structure with real examples. If you can see the structure of the PL SQL block you can see another section -which is exception section. You require to handle the exception while you are running PL SQL program. There is seperate section to handle the exceptions. There are so many oracle in built exceptions which user can handle in exception section. You will get to know about Implicit exception in detail.

What you will find in this article?

1. What is mean by Implicit Exception?
2. How to handle the implicit exception?
3. Examples of Implicit Exception

What is mean by Implicit Exception?

The Exception is nothing but the error raised during program execution. The exception will be raised during the run time not at compile time. When you compile the program it will not throw the error but at run time you will get the error is called as implicit exception.

There are two types of exceptions :

1. Implicit Exception
2. Explicit Exception

The implicit exception is exception or error occured at the run time. There are two types of implicit exceptions :

1. Predefined oracle server error : These are most usually occurred oracle errors which are 20 predefined errors.
2. Non Predefined oracle server error: Any other standard oracle errors which are not listed in predefined error are called as non predefined oracle server errors. You require to declare these kind of errors in declarative section.

Real Life Example of Implicit Exception :

–1 try to execute following block without exception
declare
v_first_name employees.first_name%type;
begin

select first_name into v_first_name
from
Hr.employees
where employee_id=1001;

— there is no emp_id=1001 in Employees table

end;

The above statement will throw the error and you will get the runtime error (exception) DATA_NOT_FOUND

How to handle implicit exceptions?

In this section I would like to explain about how to handle implicit exceptions with real life examples. There are following rules to handle implicit exceptions :

You can handle more than one exceptions at same time. You require to use the WHEN OTHERS clause. To handle the oracle exceptions you require to use the predefined names. There are following sample predefined names :

NO_DATA_FOUND

TOO_MANY_ROWS

INVALID_CURSOR

ZERO_DIVIDE

DUP_VAL_ON_INDEX

We require to check the following example which will handle multiple exceptions.

Example to handle multiple exceptions :

Declare
v_emp_id employees.employee_id%type;
begin

select employee_id into v_emp_id
from
employees
where first_name=&name;

—While executing this try to give employee name which is not present in table

exception
when no_data_found then
dbms_output.put_line(‘The Query does not retrieve records’);

when too_many_rows then
dbms_output.put_line(‘The query retrieve more than one record’);

when others then
dbms_output.put_line(‘Other ERROR’);
end;

The oracle PL SQL predefined exception has the oracle error number. Each oracle exception associated with that error number. When you run the program and at run time you will get the error code and error description.

These are following some predefined exceptions in oracle :

How to Handle Non-Predefined Exceptions?

You can handle non predefined exceptions as well by declaring the exceptions in declare section. These kind of exceptions are not defined as PL SQL exceptions . You can create exceptions with standard oracle errors using PRAGMA_EXCEPTIONS_INIT function. You can refer the predefined exception to create own non predefined exception.

You can use SQLCODE and SQLERRM statements to return the error code and Error message directly.

SQLCODE : It will return the Numeric value for error code.

SQLERRM : It will return the Error message associated with error code.

Real Life Example :

To check the real life example we can create a departments table and try to insert the value inside the department table.

desc departments;

insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
values (1,null );

–SQL Error: ORA-01400: cannot insert NULL into (“HR”.”DEPARTMENTS”.”DEPARTMENT_NAME”)

begin
insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
values (1,null );

end;

declare
e_insert exception;
pragma exception_init(e_insert,-01400);
begin
insert into departments(DEPARTMENT_ID ,DEPARTMENT_NAME)
values (1,null );

exception
when e_insert then
dbms_output.put_line(‘insert failed’);
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);

when others then
null;

end;

I hope you like this article and find this as useful article. If you like this article or if you have any questions with the same kindly comment in comments section.