0

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.

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;
  • That just what I knew before. But why is only RAISE my_fault; valid? Why not together with RAISE_APPLICATION_ERROR? – David Zurborg Feb 21 '16 at 9:57
  • @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". – Justin Cave Feb 21 '16 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.