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