EXCEPTIONS - 2

[debasisdas]

Sample Example Of Zero_divide
===============================

Declare
I Int:=&i;
J Int:=&j;
K Int;
Begin
K:=i/j;
Dbms_output.put_line(k);
Exception
--if j=0 then (dividng the number by zero)
When Zero_divide Then
Raise_application_error(-20002,'cant Divide By Zero.....!');
When Others Then
Raise_application_error(-20003,'some Other Error........!');
End;
 

Zero_divide----This exception is raised when in any number id divided by zero in arithmetic operations.

Sample Example Of No_data_found
==============================

Declare
Name Varchar2(20);
No Int:=&no;
Begin
Select Ename Into Name From Emp Where Empno=no;
Dbms_output.put_line(name);
Exception
When No_data_found Then
Raise_application_error(-20002,'no Data Is Found For This Record.....!');
When Others Then
Raise_application_error(-20003,'some Other Error........!');
End;
 

No_data_found---This exception is raised whan any select into statment fails to retrive any data from database table. This may create eror in further data processing if left unhandled.

sample example of invalid_cursor
=============================

DECLARE
CURSOR c1 IS SELECT ename FROM emp WHERE ROWNUM < 11;
name emp.ename%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line(c1%ROWCOUNT || '. ' || name);
END LOOP;
--cursor is closed
CLOSE c1;
--again trying to access the cursor variable after cursor is closed ,which raises an exception
dbms_output.put_line(c1%ROWCOUNT); 
exception
when invalid_cursor then
raise_application_error(-20001,'invalid operation in cursor');
END;
 

invalid_cursor--This explanation is self explanatory. Raises if there is any invalid cursor related operations.
Note --In the above example ,it raise the exception because the program tries to access the cursor variable %ROWCOUNT after the cursor is closed.

Sample Example Of DUP_VAL_ON_INDEX
==================================

create procedure insdept(num  number,name varchar2)
is
begin
insert into dept(deptno,dname) values (num,name);
commit;
dbms_output.put_line('One row inserted...!');
exception
when dup_val_on_index then
raise_application_error(-20001,'duplicate entry...!');
when others then
raise_application_error(-20002,'some other error occured...!');
end;
 

DUP_VAL_ON_INDEX-----This Exception is raised if a duplicate value is inserted on a primary key / unique field . Used to maintain data integrity.


Sample example showing TOO_MANY_ROWS
==================================

declare
name varchar2(20);
begin
select ename into name from emp where deptno=&deptnumber;
dbms_output.put_line(name);
exception
when NO_DATA_FOUND then
raise_application_error(-20001,'no such data found...1');
when TOO_MANY_ROWS then
raise_application_error(-20002,'more than one matching record found...!');
when OTHERS then
raise_application_error(-20003,'some unexpected error occured...!');
end;
 
 

TOO_MANY_ROWS---This exception is raised when more data is selected than that can be stored in the target variable in a select into statment.


Also check EXCEPTIONS - 3