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 
