8,127
===============================
Expand|Select|Wrap|Line Numbers
- 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;
Sample Example Of No_data_found
==============================
Expand|Select|Wrap|Line Numbers
- 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;
sample example of invalid_cursor
=============================
Expand|Select|Wrap|Line Numbers
- 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;
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
==================================
Expand|Select|Wrap|Line Numbers
- 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;
Sample example showing TOO_MANY_ROWS
==================================
Expand|Select|Wrap|Line Numbers
- 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;
Also check EXCEPTIONS - 3
