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