4

Want to define a custom message for a user defined exception. What I have now:

declare 
  e exception;
  pragma exception_init (e, -20100);
begin 
  raise e;
end;

ORA-20100: 
ORA-06512: at line 5

What I want:

exec dbms_output.put_line(userenv('COMMITSCN'))

ORA-01725: USERENV('COMMITSCN')  not allowed here
ORA-06512: at "SYS.STANDARD", line 202
ORA-06512: at line 1

at "SYS.STANDARD", line 202 we can see:

raise USERENV_COMMITSCN_ERROR;

The exception is defined in specification as:

  -- Added for USERENV enhancement, bug 1622213.
  USERENV_COMMITSCN_ERROR exception;
    pragma EXCEPTION_INIT(USERENV_COMMITSCN_ERROR, '-1725');

The questions are:

  1. How the message "USERENV('COMMITSCN') not allowed here" is defined?

  2. How to do it in my code?

Thanks a lot for your answers!

Nick Legend
  • 789
  • 1
  • 7
  • 21

1 Answers1

3

Using the RAISE_APPLICATION_ERROR procedure to raise the exception allows you to associate a message with the error:

DECLARE
  e EXCEPTION;

  PRAGMA EXCEPTION_INIT (e, -20100);
BEGIN
  RAISE_APPLICATION_ERROR(-20100, 'This is the user-supplied message');
EXCEPTION
  WHEN e THEN
    DBMS_OUTPUT.PUT_LINE('Caught e: ' || SQLCODE || ' ' || SQLERRM);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Caught something else: ' || SQLCODE || ' ' || SQLERRM);
END;

Documentation here - in particular, read the section titled "Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR".

Share and enjoy.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • 1
    Thanks Bob! That's what I already know and what is widely discussed on the Internet. But I'm curious how the exception message in SYS.STANDARD is implemented and whether I can do the same in my code. – Nick Legend Jul 23 '14 at 12:13
  • To the best of my knowledge there's no mechanism that allows you to do that. Best of luck. – Bob Jarvis - Слава Україні Jul 23 '14 at 15:40
  • No, there is no mechanism within the database yet (for future reference, the latest version is 12.1.0.2 at the moment) that allows you to add an error message to your exception, this is reserved only for Oracle internal errors at the moment. So the only way you have is the above one with RAISE_APPLICATION_ERROR – gvenzl Jul 25 '14 at 10:28