Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53

    Unanswered: invalid_number & value_error

    can anyone tell the diff between these exceptions
    invalid_number & value_error

    think that in procedural stmts we get only value_error,
    in that case how to differentiate(identify) them

    Thanks in advance
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Luckily for you, Oracle document all these things!!

  3. #3
    Join Date
    Feb 2004
    Posts
    108
    Break and learn

    DECLARE
    n1 VARCHAR2(1) NOT NULL := 'x' ;
    BEGIN
    n1 := 'LongString' ;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM) ;
    END ;

    And ...

    SELECT TO_NUMBER('1234,5678.999') FROM dual;

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I see what you mean: VALUE_ERROR is raised for both types of error. You can tell which is which by lookign at SQLERRM:
    Code:
    SQL> declare
      2    x number(2);
      3  begin
      4    x := 123;
      5  exception
      6    when value_error then say ('value error: '||sqlerrm);
      7    when invalid_number then say ('invalid number: '||sqlerrm);
      8  end;
      9  /
    value error: ORA-06502: PL/SQL: numeric or value error: number precision too large
    
    PL/SQL procedure successfully completed.
    
    SQL> declare
      2    x number(2);
      3  begin
      4    x := 'x';
      5  exception
      6    when value_error then say ('value error: '||sqlerrm);
      7    when invalid_number then say ('invalid number: '||sqlerrm);
      8  end;
      9  /
    value error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    
    PL/SQL procedure successfully completed.
    (say is just my shorthand for dbms_output.put_line!)

  5. #5
    Join Date
    Feb 2004
    Posts
    108
    Yea, I saw that. It looks weired.
    I could not reproduce - ORA-01722 invalid number

  6. #6
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    thanks guys
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

Posting Permissions

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