Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM

Question

Is it possible to create user-defined exceptions and be able to change the SQLERRM?

For example:

DECLARE
    ex_custom       EXCEPTION;
BEGIN
    RAISE ex_custom;
EXCEPTION
    WHEN ex_custom THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

The output is "User-Defined Exception". Is it possible to change that message?

EDIT: Here is some more detail.

I hope this one illustrates what I'm trying to do better.

DECLARE
    l_table_status      VARCHAR2(8);
    l_index_status      VARCHAR2(8);
    l_table_name        VARCHAR2(30) := 'TEST';
    l_index_name        VARCHAR2(30) := 'IDX_TEST';
    ex_no_metadata      EXCEPTION;
BEGIN

    BEGIN
        SELECT  STATUS
        INTO    l_table_status
        FROM    USER_TABLES
        WHERE   TABLE_NAME      = l_table_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- raise exception here with message saying
            -- "Table metadata does not exist."
            RAISE ex_no_metadata;
    END;

    BEGIN
        SELECT  STATUS
        INTO    l_index_status
        FROM    USER_INDEXES
        WHERE   INDEX_NAME      = l_index_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- raise exception here with message saying
            -- "Index metadata does not exist."
            RAISE ex_no_metadata;
    END;

EXCEPTION
    WHEN ex_no_metadata THEN
        DBMS_OUTPUT.PUT_LINE('Exception will be handled by handle_no_metadata_exception(SQLERRM) procedure here.');
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

In reality, there are dozens of those sub-blocks. I'm wondering if there's a way to have a single user-defined exception for each of those sub-blocks to raise, but have it give a different message, instead of creating a separate user-defined exception for each sub-block.

In .NET, it would be sort of like having a custom exception like this:

    public class ColorException : Exception
    {
        public ColorException(string message)
            : base(message)
        {
        }
    }

And then, a method would have something like this:

        if (isRed)
        {
            throw new ColorException("Red is not allowed!");
        }

        if (isBlack)
        {
            throw new ColorException("Black is not allowed!");
        }

        if (isBlue)
        {
            throw new ColorException("Blue is not allowed!");
        }

score 158 · Accepted Answer · answered May 16 '11 at 16:39

Yes. You just have to use the RAISE_APPLICATION_ERROR function. If you also want to name your exception, you'll need to use the EXCEPTION_INIT pragma in order to associate the error number to the named exception. Something like

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    ex_custom EXCEPTION;
  3    PRAGMA EXCEPTION_INIT( ex_custom, -20001 );
  4  begin
  5    raise_application_error( -20001, 'This is a custom error' );
  6  exception
  7    when ex_custom
  8    then
  9      dbms_output.put_line( sqlerrm );
 10* end;
SQL> /
ORA-20001: This is a custom error

PL/SQL procedure successfully completed.

Precisely what I need! I guess I made my edit while you had already answered my question. Thank you very much. — tgxiii, May 16 '11 at 17:18

score 43 · Answer 2 · answered May 16 '11 at 16:35

You could use RAISE_APPLICATION_ERROR like this:

DECLARE
    ex_custom       EXCEPTION;
BEGIN
    RAISE ex_custom;
EXCEPTION
    WHEN ex_custom THEN
        RAISE_APPLICATION_ERROR(-20001,'My exception was raised');
END;
/

That will raise an exception that looks like:

ORA-20001: My exception was raised

The error number can be anything between -20001 and -20999.

score 26 · Answer 3 · answered Jul 17 '13 at 18:51

I usually lose track of all of my -20001-type error codes, so I try to consolidate all my application errors into a nice package like such:

SET SERVEROUTPUT ON

CREATE OR REPLACE PACKAGE errors AS
  invalid_foo_err EXCEPTION;
  invalid_foo_num NUMBER := -20123;
  invalid_foo_msg VARCHAR2(32767) := 'Invalid Foo!';
  PRAGMA EXCEPTION_INIT(invalid_foo_err, -20123);  -- can't use var >:O

  illegal_bar_err EXCEPTION;
  illegal_bar_num NUMBER := -20156;
  illegal_bar_msg VARCHAR2(32767) := 'Illegal Bar!';
  PRAGMA EXCEPTION_INIT(illegal_bar_err, -20156);  -- can't use var >:O

  PROCEDURE raise_err(p_err NUMBER, p_msg VARCHAR2 DEFAULT NULL);
END;
/

CREATE OR REPLACE PACKAGE BODY errors AS
  unknown_err EXCEPTION;
  unknown_num NUMBER := -20001;
  unknown_msg VARCHAR2(32767) := 'Unknown Error Specified!';

  PROCEDURE raise_err(p_err NUMBER, p_msg VARCHAR2 DEFAULT NULL) AS
    v_msg VARCHAR2(32767);
  BEGIN
    IF p_err = unknown_num THEN
      v_msg := unknown_msg;
    ELSIF p_err = invalid_foo_num THEN
      v_msg := invalid_foo_msg;
    ELSIF p_err = illegal_bar_num THEN
      v_msg := illegal_bar_msg;
    ELSE
      raise_err(unknown_num, 'USR' || p_err || ': ' || p_msg);
    END IF;

    IF p_msg IS NOT NULL THEN
      v_msg := v_msg || ' - '||p_msg;
    END IF;

    RAISE_APPLICATION_ERROR(p_err, v_msg);
  END;
END;
/

Then call errors.raise_err(errors.invalid_foo_num, 'optional extra text') to use it, like such:

BEGIN
  BEGIN
    errors.raise_err(errors.invalid_foo_num, 'Insufficient Foo-age!');
  EXCEPTION
    WHEN errors.invalid_foo_err THEN
      dbms_output.put_line(SQLERRM);
  END;

  BEGIN
    errors.raise_err(errors.illegal_bar_num, 'Insufficient Bar-age!');
  EXCEPTION
    WHEN errors.illegal_bar_err THEN
      dbms_output.put_line(SQLERRM);
  END;

  BEGIN
    errors.raise_err(-10000, 'This Doesn''t Exist!!');
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
  END;
END;
/

produces this output:

ORA-20123: Invalid Foo! - Insufficient Foo-age!
ORA-20156: Illegal Bar! - Insufficient Bar-age!
ORA-20001: Unknown Error Specified! - USR-10000: This Doesn't Exist!!

This is a good practice. Two minor issues on [raise_application_error](http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS99960) thought: 1) the size of 2nd parameter is limited to 2048 bytes and 2) I'd prefer 3rd parameter to be `true` (instead of default `false`) to get a full stack trace. — user272735, Mar 05 '15 at 05:12

score 5 · Answer 4 · edited Nov 11 '12 at 13:58

5

declare
   z exception;

begin
   if to_char(sysdate,'day')='sunday' then
     raise z;
   end if;

   exception 
     when z then
        dbms_output.put_line('to day is sunday');
end;

edited Nov 11 '12 at 13:58

Taryn

242,637
56
362
405

answered Nov 11 '12 at 13:54

Nagaraju Nampally

65
1
1

score 2 · Answer 5 · edited Dec 24 '18 at 10:23

2

create or replace PROCEDURE PROC_USER_EXP 
AS
duplicate_exp EXCEPTION;
PRAGMA EXCEPTION_INIT( duplicate_exp, -20001 );
LVCOUNT NUMBER;
BEGIN
  SELECT COUNT(*) INTO LVCOUNT FROM JOBS WHERE JOB_TITLE='President';
  IF LVCOUNT >1 THEN 
   raise_application_error( -20001, 'Duplicate president customer excetpion' );
  END IF;

  EXCEPTION 
  WHEN duplicate_exp THEN 
  DBMS_OUTPUT.PUT_LINE(sqlerrm);
END PROC_USER_EXP;

ORACLE 11g output will be like this:

Connecting to the database HR. 
ORA-20001: Duplicate president customer excetpion 
Process exited. 
Disconnecting from the database HR

edited Dec 24 '18 at 10:23

APC

144,005
19
170
281

answered Dec 09 '15 at 16:23

Raj Sharma

141
1
4

ORACLE 11g output will be like this:-Connecting to the database HR. ORA-20001: Duplicate president customer excetpion Process exited. Disconnecting from the database HR. – Raj Sharma Dec 09 '15 at 16:24
This is good stuff except (ironically) for the EXCEPTION block. In almost all circumstances exceptions handled in a WHEN clause should be re-raised to the calling program. The calling program needs to know that the called routine failed. Simply using DBMS_OUTPUT is not good enough, because the calling program might not be able to process the serveroutput buffer and/or because it doesn't force the calling program to acknowledge the error. – APC Dec 24 '18 at 10:27

Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM

5 Answers5

Linked