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

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> edWrote 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 errorPL/SQL procedure successfully completed.

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.

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 ONCREATE 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!!