06 February 2009

Value Error and Invalid Number

Currently I'm in the process on reviewing some chapters on an upcoming book... more on that in a later blog, 'cause I am not sure if I can talk about it or not.

One of the chapters is on "Exceptions" and I noticed a line from the Oracle documentation which I didn't notice before. The VALUE_ERROR (ORA-6502) and INVALID_NUMBER (ORA-1722) are predefined exception, which can be handled by name in your exception handler.
Quote from the docs:
"...In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)"

Let's first check the latter part, about the SQL statements:


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


Now let's check the procedural part, in PL/SQL.
Based on the description, this statement would cause a VALUE_ERROR exception

SQL> declare
2 n number;
3 begin
4 n := 'a';
5 exception
6 when value_error
7 then
8 dbms_output.put_line ('Value Error');
9 end;
10 /
Value Error

PL/SQL procedure successfully completed.

...and it does.

But what if you would use a SQL statement in PL/SQL?
Would this also raise a VALUE_ERROR?

SQL> declare
2 n number;
3 begin
4 select 'a'
5 into n
6 from dual
7 ;
8 exception
9 when value_error
10 then
11 dbms_output.put_line ('Value Error');
12 end;
13 /
Value Error

PL/SQL procedure successfully completed.


Yes, it does. In accordance with the documentation, doing a conversion in procedural statement will raise the VALUE_ERROR exception,...
One last option, let's use the first SQL statement from this blog and use it in PL/SQL:

SQL> declare
2 n number;
3 begin
4 select to_number('a')
5 into n
6 from dual
7 ;
8 exception
9 when value_error
10 then
11 dbms_output.put_line ('Value Error');
12 when invalid_number
13 then
14 dbms_output.put_line ('Invalid Number');
15 end;
16 /
Invalid Number

PL/SQL procedure successfully completed.


As you can see, it raises the INVALID_NUMBER exception. I expected that it would raise the VALUE_ERROR as well.

Tracing the statements reveals the explanation. With the implicit datatype conversion, this is the statement that is executed by the SQL engine (I added some text to the statement to identify the actual statement):

SELECT /*+ implicit_conversion */'a'
FROM
DUAL


and with the explicit datatype conversion, this SQL statement gets executed

SELECT /*+ explicit_conversion */ TO_NUMBER('a')
FROM
DUAL


Sometimes it's too easy to ignore these little things. I learned something about exceptions again.
Link:
Oracle Documentation on Predefined PL/SQL Exceptions

10 comments:

  1. Didn't you have to sign an NDA (No Damn Announcements) agreement for proofreading ;-)

    ReplyDelete
  2. No,... but you can probably guess which book it is... ;-)

    ReplyDelete
  3. Don't understand your comparison between
    select /*+ hint1 */ 'a'
    with
    select /*+ hint2 */ to_number('a')
    Obviously I can select a string from dual, however I can't convert a string to number and that's independent of the hint.

    ReplyDelete
  4. Well, it's not really a hint. Looks like a hint because of the /*+ ... */ notation, but it is not.
    The reason I put these there was just to identify the individual SQL statements and find them in the trace file.

    ReplyDelete
  5. Yes, I see: No hint :-)
    Nevertheless, why the comparison of a valid statement with an invalid one and where is the implicit conversion of the first statement?
    You put in a sting ('a') and get a string 'a'. There's no conversion.

    ReplyDelete
  6. Of course, you are right. Using the "conversion" in the non-hint was a (maybe) wrong choice of words. The point I was trying to make is that this statement:
    select 'a' into n from dual;
    raises a VALUE_ERROR. The string ('a) is attempted to be converted into a NUMBER in PL/SQL, and not in SQL.
    When you try to "do the right thing" and explicitly convert it to a NUMBER in SQL, using the TO_NUMBER function you get a different exception INVALID_NUMBER.
    Does this clarify my choice of words - explicit and implicit conversion?

    ReplyDelete
  7. SUPERB EXPLANATION :) Thanks a lot :)

    ReplyDelete
  8. of course!!! for that reason my program don't work, now I can reprogram the code and see what happen, thanks for the help.

    ReplyDelete