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
handling null value exception
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.
exception handling in a stored procedure not working
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