Types of Exceptions
1. Pre Defined Exceptions
2. User Defined Exceptions
Pre Defined Exceptions
These exceptions are used to handle some logical errors known to the system are pre defined. The following are some of the important pre defined Exceptions
- no_data_found
This Exception raises when there is no rows to be retrieved from a table
according to given condition
- dup_val_on_index
This Exception raises when ever you try to store duplicate values into a
table, which has been indexed (unique indexed)
- cur_already_open
This Exception raises when ever your program attempts to open an already
opened cursor.
A cursor must be closed before it can be re opened. A cursor for loop
automatically opens the cursor to which it refers. So Your program can not open that cursor inside the loop
- invalid_cursor
This Exception raises when ever your program attempts an illegal cursor operation, such as closing an un opened cursor
- zero_divide
This Exception raises when ever your program attempts to divide a number
by zero
- program_error
This Exception raises when ever PL/SQL has internal problem
- storage_error
This Exception raises when ever PL/SQL runs out of memory
- too_many_rows
This Exception raises when ever a select statement returns more than one
row
- login_denied
This Exception raises when ever your program attempts to logon to oracle
with an invalid user name and/or password
- value_error
This Exception raises when ever an arithmetic conversion or size constraint error occurs
For ex, when your program selects a column value into a variable. If the value is longer than the declared length of the variable, pl/sql abords the assignment and raise the exception value_error
Programs On Exceptions
1. Write a program for handling an error when we try to open an already opened cursor
declare
cursor c1 is select * from emp;
x emp%rowtype;
begin
open c1;
fetch c1 into x;
dbms_output.put_line(x.empno||' '||x.ename);
--open c1;
close c1;
exception
when cursor_already_open then
dbms_output.put_line(' sorry friend, cursor already opened');
end;
/
2. Write a program to display a message when no data found in the main table
declare
e emp%rowtype;
begin
select * into e from emp where empno=&empno;
dbms_output.put_line('Empno Ename job');
dbms_output.put_line(e.empno||' '||e.ename||' '||e.job);
exception
when No_data_found then
dbms_output.put_line(' sorry friend, no data found');
end;
/
3. Write a program for handling an error when we insert a duplicate value into a column.
declare
n sample%rowtype;
begin
insert into sample values(&sno,'&sname','&class');
dbms_output.put_line('Record Inserted....');
exception
when dup_val_on_index then
dbms_output.put_line(' sorry friend, you entered a duplicate value');
end;
/
Note : create a table with unique constraint, for dup_val_on_index,
- create table sample(sno number(3),sname varchar2(10));
- CREATE UNIQUE INDEX IDX_SAM ON SAMPLE(SNO);
4. Write a program for handling an error when we try to close an un opened cursor
declare
cursor c1 is select * from emp;
begin
--open c1;
dbms_output.put_line(' cursor is Opened');
close c1;
exception
when invalid_cursor then
dbms_output.put_line(' sorry friend, No such cursor is opened');
end;
/
Note : if u remove comment for open c1 then output is cursor is opened, other wise the you will get “sorry friend, no such cursor is opened ..”, since you r trying to close an unopened cursor
5. Write a program for handling an error when we try to divide a value with zero
declare
a number:=&a;
b number:=&b;
c number;
begin
c:=a/b;
dbms_output.put_line(c);
exception
when zero_divide then
dbms_output.put_line('Zero Divide error');
end;
/
6. Write a program for handling an error when ever the program retrieves too many rows
declare
e emp%rowtype;
begin
select * into e from emp;
exception
when too_many_rows then
dbms_output.put_line(' sorry friend, too many rows are selected');
end;
/
User Defined Exceptions
A user can define or create exceptions, which must be raised automatically, because the system does not knows when to execute the user defined errors. There are 3 steps while working with user defined exceptions
1. Creating Exception
2. Raising Exception
3. Handling Exception
The main purpose of user defined exceptions is, we can display a message in predefined error format in the instances not known to the system or that are not predefined
Syntax :
Declare
<Exception Name> Exception;
begin
Raise Exception;
Exception
When <Exception name> then
Message
End;
/
Raise_Application_Error
This is a keyword, which is used to generate an error message in pre defined error format for the given user defined error
Syntax :
Raise_Application_Error(Error number, ‘ message ‘);
Example :
Raise_Application_Error(-20173, ‘ Some Error ‘);
Note : Error Number for (user defined Exceptions) must be in between –20001 to -20999
1. Write a program for creating an exception and raising it when the basic is <3000, while inserting rows into a table and also handle it to perform any other action
Declare
invalid_salary exception;
s employee.sal%type:=&sal;
begin
if s<3000 then
raise invalid_salary;
else
insert into employee(sal) values (s);
dbms_output.put_line(' Record Inserted.....');
end if;
exception
when invalid_salary then
dbms_output.put_line(' salary must be >3000 ');
end;
/
2. Write a program for creating an exception and raising it when ever you try to insert any negative number into a table
Declare
invalid_number exception;
e employee.empno%type:=&empno;
begin
if e<0 then
raise invalid_number;
else
insert into employee(empno) values (e);
dbms_output.put_line(' Record Inserted.....');
end if;
exception
when invalid_number then
dbms_output.put_line(' Number must be positive ');
end;
/
No comments:
Post a Comment