DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: numeric or value error, execption handling

  1. #1
    Join Date
    Apr 2003
    Posts
    29

    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

  2. #2
    Join Date
    May 2003
    Posts
    12

    Lightbulb

    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');

  3. #3
    Join Date
    Apr 2003
    Posts
    29

    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.

  4. #4
    Join Date
    May 2003
    Posts
    12

    Lightbulb

    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.

  5. #5
    Join Date
    Apr 2003
    Posts
    29

    reply

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

  6. #6
    Join Date
    Apr 2003
    Posts
    29

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width