CODEWITHSUNDEEP
oracle-databaseexceptionplsql
Soon
Soon

Reputation: 501

Oracle INVALID_NUMBER exception in my function does not work

The Oracle function below assign 'A' to number variable. But the Exception clause does not catch INVALID_NUMBER system exception.

CREATE OR REPLACE FUNCTION TEST2
    (P1 IN VARCHAR2)
RETURN NUMBER AS V_VALUE NUMBER;
BEGIN
    SELECT(
        --SELECT 1/TO_NUMBER(P1)
        SELECT 'A'
        FROM DUAL
        )
    INTO V_VALUE
    FROM DUAL;
    RETURN V_VALUE;

    EXCEPTION
    WHEN INVALID_NUMBER THEN  -- If I change INVALID_NUMBER to OTHERS, then it works.
      RETURN -1;      
END;
/


SELECT TEST2('1') FROM DUAL;

When I change INVALID_NUMBER to OTHERS, then it works.

How to modify above code to chatch the INVALID_NUMBER exception?

Upvotes: 1

Views: 9012

Answers (2)

Littlefoot
Littlefoot

Reputation: 142968

Some more info (to what Barbaros already told you).

The INVALID_NUMBER Exception (ORA-01722) occurs in a SQL statement when the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.

Compare

SQL> select 'a'/2 from dual;
select 'a'/2 from dual
       *
ERROR at line 1:
ORA-01722: invalid number

to 

SQL> declare
  2    l_num number;
  3  begin
  4    l_num := 'a' / 2;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

to

SQL> declare
  2    l_num number;
  3  begin
  4    select 'a' / 2 into l_num from dual;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 4

Isn't that nice of Oracle? More or less the same thing, but different response.

Upvotes: 2

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

Because you get VALUE_ERROR when ORA-06502 raised as occurs in your case. So, replace INVALID_NUMBER with VALUE_ERROR.

as an example; if the SELECT statement was :

SELECT 5
  INTO V_VALUE
  FROM DUAL
 WHERE TO_NUMBER('A')=1;

you'd get INVALID_NUMBER(ORA-01722) exception.

Upvotes: 4

Related Questions