Using exception with raise_application_error

Question

I'm developing with Oracle 11g. How can I use a named exception in RAISE_APPLICATION_ERROR like this:

DECLARE
 my_fault EXCEPTION;
 PRAGMA EXCEPTION_INIT (my_fault, -20000);
BEGIN
 RAISE_APPLICATION_ERROR(my_fault, 'my error message');
END;

But I got an error. I have to use the error code explicity. The Oracle Database Documentation says:

Restriction on exception

You can use exception only in an EXCEPTION_INIT pragma, RAISE statement, RAISE_APPLICATION_ERROR invocation, or exception handler.

I'm confused.

Answer 1

There are two ways to raise exceptions in Oracle. If you want to specify your own message, you wouldn't declare a local variable of type exception. You'd simply put the error code in your raise_application_error call

BEGIN
 raise_applicaton_error( -20001, 'Some message' );
END;

If you want to declare a local exception type, then you wouldn't use raise_application_error to throw it. You'd just use raise. But then you won't be able to specify a custom message.

DECLARE
 my_fault EXCEPTION;
 PRAGMA EXCEPTION_INIT (my_fault, -20000);
BEGIN
 RAISE my_fault;
END;

If you just don't like hard-coding the error code in your raise_application_error call, you can put that in a local integer variable

declare
 myfault_code integer := -20001;
begin
 raise_application_error( myfault_code, 'Some error' );
end;

• David Zurborg: That just what I knew before. But why is only RAISE my_fault; valid? Why not together with RAISE_APPLICATION_ERROR?
• Justin Cave: @DavidZurborg - I'm not sure I understand the question. You can use raise_application_error but not with a local exception type. You can use raise with a local exception type but not with a custom error message. If you are asking why raise_application_error doesn't use local exception types, I'm not sure what you are looking for other than "because that's the way Oracle is designed".