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!");         } 

Answer 1

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. 

Answer 2

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.