PL/SQL VALUE_ERROR exception not raised where expected

Question

I am sure my question has a simple theoretical answer but I cannot find it.

I have a procedure which accepts as parameter a NUMBER. It also have the VALUE_ERROR and OTHERS exceptions:

create or replace procedure test( p1 number) is
begin
    null;
exception
    when VALUE_ERROR then
        RAISE_APPLICATION_ERROR(-20001, 'value_error');
    when others then
        RAISE_APPLICATION_ERROR(-20000, 'others');
end;

I am executing the procedure with a VARCHAR2 paramter:

execute test('a');

... an I expect that the error message displayed to be

ORA-20001 value_error

but, unfortunately, I got:

Error report - ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). *Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Can anyone explain this, or share a link where it is explained why I do not receive expected error message?

Thank you very much,

Because exception section is never reached. When you call your procedure passing in a character when a number is expected PL/SQL engine is trying to implicitly convert the character to a number and if it cant, exception is raised - procedure is never executed. If you want to trap that exception call your procedure inside another PL/SQL block with it's own exception handling section. — Nick Krasnov
As it raises the exception because of not being able to perform an implicit conversion, why it doesn't raise the VALUE_ERROR one, which is a predefined one?! — mikcutu
VALUE_ERROR = ORA-06502 PL/SQL: numeric or value error what else do you expect to see? — Nick Krasnov
I would expect to see the small message I defined in VALUE_ERROR exception handler from my code instead of entire text which normally occurs for ORA-06502. — mikcutu
Have your read my first comment? you want to see your custom message put your procedure inside another pl/sql block and trap that error there? Parameter conversion exception cannot be caught inside the same PL/SQL block. — Nick Krasnov

Kacper Kacper · Accepted Answer · 2016-12-25T11:05:25

As Nicholas mentioned you don't get your message because the exception is thrown not inside the procedure but before executing it.

Let's look at an example:

create or replace procedure test( p1 number) is
begin
    null;
exception
    when VALUE_ERROR then
        RAISE_APPLICATION_ERROR(-20001, 'PROC value_error');
    when others then
        RAISE_APPLICATION_ERROR(-20000, 'PROC others');
end;
/
begin
  test('a');
exception
    when VALUE_ERROR then
        RAISE_APPLICATION_ERROR(-20001, 'OUT value_error');
    when others then
        RAISE_APPLICATION_ERROR(-20000, 'OUT others');
end;

What happens here is that you're calling a procedure that requires number as parameter so Oracle tries conversion during execution of anonymous block. You can't see the message from the procedure because before entering the procedure the conversion exception is thrown.

Now let's see what happens if we change the procedure:

create or replace procedure test( p1 varchar2) is
param number;
begin
    param := p1;
exception
    when VALUE_ERROR then
        RAISE_APPLICATION_ERROR(-20001, 'PROC value_error');
    when others then
        RAISE_APPLICATION_ERROR(-20000, 'PROC others');
end;
/
begin
  test('a');
exception
    when VALUE_ERROR then
        RAISE_APPLICATION_ERROR(-20001, 'OUT value_error');
    when others then
        RAISE_APPLICATION_ERROR(-20000, 'OUT others');
end;

Or just:

begin
  test('a');
end;

to see the error thrown in the procedure.

Now the procedure requires number within its body; when execution reaches that point it throws the conversion error, from within the procedure itself.

PL/SQL VALUE_ERROR exception not raised where expected

1 Answers