We have a table called dept of the following structure in oracle
Name Null? Type
------------------------------- -------- ----
DEPTNO NOT NULL NUMBER(2)
DNAME NOT NULL VARCHAR2(14)
LOC NOT NULL VARCHAR2(13)

we want to fire multiple exceptions
1. exception for unique constraint
2.when we try to insert a varchar value into a field of type number
3.when we try to enter null values into not null fields
We have achieved the first case, but cant achieve the second and the third. Please do send the code

SQL> ED
Wrote file afiedt.buf

1 create or replace procedure dup_val(var1 out number) is
2 str number := 'A';
3 begin
4 insert into dept values(str,'NAME','PUNE');
5 exception when dup_val_on_index
6 then var1 := 1;
7 WHEN OTHERS THEN
8 IF SQLCODE = 1722 then
9 var1 := 2;
10 END IF;
11* end;
SQL> /
Procedure created.

SQL> VARIABLE X NUMBER
SQL> EXEC DUP_VAL(:X);
begin DUP_VAL(:X); end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.DUP_VAL", line 2
ORA-06512: at line 1