numeric or value error, execption handling

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

Procedure : dup_val
line : 2

you have declared 'str' as a number variable.
So it is not accepting 'A'.
ORA-06502: PL/SQL: numeric or value error

Try avoiding the variable 'str', and pass a constant literal to the insert statement at line 4, like:

insert into dept values('A','NAME','PUNE');

ya, the problem of numeric or value error has been solved. Thankz for the solution. Can you give a solution for the following.
1.when we try to enter null values into not null fields of oracle
then how to handle the exception. Do we have to write a user defined exception, if so what is the code for it, or is there a system exception for it.

You can create your own exception for any Oracle error messages.
The following PL/SQL block takes ORA-01400 ( "Can not insert NULL into" ) as an example.

SQL>create table temp_null_val ( temp_name varchar2(10) not null );
SQL>set serveroutput on
SQL> declare
2 null_value exception;
3 pragma exception_init ( null_value, -1400 );
4 begin
5 insert into temp_null_val values ( NULL );
6 exception
7 when null_value then
8 dbms_output.put_line ( 'Trying to insert a null value into non null column; insert failed' );
9* end;
10 /

Output:

Trying to insert a null value into non null column; insert failed

PL/SQL procedure successfully completed.

Thankz a lot for your help.I could get the exception for null values.

I need to use a stored procedure to insert values into a database, and the invalid_number exception is not working, when I try to insert a varchar into dept(numeric field).
Please help.

create or replace procedure dept1(dep in number,
deptname in varchar2,
location in varchar2,
var1 out number) as

null_value exception;
numeric_value exception;
pragma exception_init(null_value, -1400);
begin
insert into dept values(dep,deptname,location);

commit;
var1:= 0;
exception when dup_val_on_index
then var1 := 1;
when invalid_number then
var1 := 2;
when null_value then
var1 := 3;
end;
/

Procedure created.

SQL> var a number
SQL> exec dept1('A','EDP','PUNE',:A);
begin dept1('A','EDP','PUNE',:A); end;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1