CODEWITHSUNDEEP
oracle-databaseexceptionmessageuser-defined
Nick Legend
Nick Legend

Reputation: 1038

User-defined execption with custom message

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!

Upvotes: 4

Views: 3204

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 3

Related Questions