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

User · Answer

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

User · Answer

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  gt  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  gt  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

User · Answer

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

User · Answer

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  gt 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

User · Answer

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 gt  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 gt    ORA-20001  This is a custom error  PL SQL procedure successfully completed

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

Examples related to oracle

Examples related to exception

Examples related to plsql

Examples related to custom-exceptions