Oracle Live SQL - Script: Generate Named Exceptions

Statement 1

PROMPT Creating Table 'mam_named_exceptions'

Unsupported Command

Statement 2

CREATE TABLE mam_named_exceptions(
     mne_sqlcode         NUMBER(7,0) NOT NULL
    ,mne_errorname       VARCHAR2(30) NOT NULL
    ,mne_errorlevel      NUMBER(2,0) NOT NULL
    ,mne_description     VARCHAR2(2000) NOT NULL
    ,mne_errortext       VARCHAR2(2000) NOT NULL
    )

Table created.

Statement 3

COMMENT ON COLUMN mam_named_exceptions.mne_sqlcode IS 'SQL_CODE of the error'

Statement processed.

Statement 4

COMMENT ON COLUMN mam_named_exceptions.mne_errorname IS 'Registered name of the named exception'

Statement processed.

Statement 5

COMMENT ON COLUMN mam_named_exceptions.mne_errortext IS 'error text. Parametrise text with ~  e.g. "Invalid values: ~ to ~ overlaps with existing intervals". Add the parameter values to your error text when you raise an error "|value1|value2|value3"'

Statement processed.

Statement 6

COMMENT ON COLUMN mam_named_exceptions.mne_errorlevel IS 'Severity of the errors from Debug to Fatal 0-5'

Statement processed.

Statement 7

COMMENT ON COLUMN mam_named_exceptions.mne_description IS 'Description of the error, e.g. "Intervals must not overlap"'

Statement processed.

Statement 8

COMMENT ON TABLE mam_named_exceptions IS 'Managing Named Exceptions.'

Statement processed.

Statement 9

PROMPT Creating Primary Key on 'mam_named_exceptions'

Unsupported Command

Statement 10

ALTER TABLE mam_named_exceptions ADD
    CONSTRAINT mne_pk
    PRIMARY KEY (mne_sqlcode)

Table altered.

Statement 11

PROMPT Creating Unique Key on 'mam_named_exceptions'

Unsupported Command

Statement 12

ALTER TABLE mam_named_exceptions ADD
    CONSTRAINT mne_uk
    UNIQUE (mne_errorname)

Table altered.

Statement 13

INSERT INTO mam_named_exceptions (mne_sqlcode,mne_errorname,mne_errorlevel,mne_errortext,mne_description)
VALUES (-20500,'stop',5,'Termination of the program execution. See log messages','Log sufficient information before raising this error. Calling programs can safely assume that the error has aready been logged.')

1 row(s) inserted.

Statement 14

INSERT INTO mam_named_exceptions (mne_sqlcode,mne_errorname,mne_errorlevel,mne_errortext,mne_description)
VALUES (-20501,'outOfRange',5,'The value of ~ is out of range. Expected ~ to ~, is ~.','The value of a parameter is not within the expected range.')

1 row(s) inserted.

Statement 15

INSERT INTO mam_named_exceptions (mne_sqlcode,mne_errorname,mne_errorlevel,mne_errortext,mne_description)
VALUES (-20502,'notGapless',5,'The intervals must have no gaps.','The intervals must have no gaps.')

1 row(s) inserted.

Statement 16

COMMIT

Statement processed.

Statement 17

CREATE OR REPLACE PACKAGE mam_exceptions
IS

    c_packageName       CONSTANT VARCHAR2(30)   := 'mam_exceptions';

/*+*Begin of generated code. Do not remove this comment!*+*/
    e_stop EXCEPTION;
    c_stop CONSTANT INTEGER := -20500;
    PRAGMA EXCEPTION_INIT (e_stop, -20500);

    e_outOfRange EXCEPTION;
    c_outOfRange CONSTANT INTEGER := -20501;
    PRAGMA EXCEPTION_INIT (e_outOfRange, -20501);

    e_notGapless EXCEPTION;
    c_notGapless CONSTANT INTEGER := -20502;
    PRAGMA EXCEPTION_INIT (e_notGapless, -20502);

FUNCTION getStop
    RETURN INTEGER;

FUNCTION getOutOfRange
    RETURN INTEGER;

FUNCTION getNotGapless
    RETURN INTEGER;

/*+*End of generated code. Do not remove this comment!*+*/

/**
* @desc raise_application_error with decoding of the error text
*           for unregistered SQLCODEs
* @param p_sqlcode      SQLCODE / mne_sqlcode
* @param p_text         error message
* @param p_keepErrorStack  Keep error stack
*/
PROCEDURE rae
    (
     p_sqlcode          IN mam_named_exceptions.mne_sqlcode%TYPE
    ,p_text             IN mam_named_exceptions.mne_errortext%TYPE
    ,p_keepErrorStack   IN BOOLEAN  := FALSE
    );

/**
* @desc Check whether the SQLCODE is a registered exception
* @param p_sqlcode  SQLCODE
* @return TRUE, if SQLCODE in mam_named_exceptions, else FALSE
*/
FUNCTION isNamedException
    (
     p_sqlcode          IN mam_named_exceptions.mne_sqlcode%TYPE
    )
    RETURN BOOLEAN;

/**
* @desc get row from mam_named_exceptions for SQLCODE
* @param p_sqlcode SQLCODE
* @return Row from mam_named_exceptions, NULL if SQLCODE is not registered
*/
FUNCTION getNamedException
    (
     p_sqlcode          IN mam_named_exceptions.mne_sqlcode%TYPE
    )
    RETURN mam_named_exceptions%ROWTYPE;

/**
* @desc get message text for SQLCODE
*       Replace parameters in message text
* @param p_sqlcode  SQLCODE
* @param p_arg      Parameters for parametrised texts
* @return message text
*/
FUNCTION getMessagetext
    (
     p_sqlcode          IN mam_named_exceptions.mne_sqlcode%TYPE
    ,p_arg              IN mam_named_exceptions.mne_errortext%TYPE := NULL
    )
    RETURN mam_named_exceptions.mne_errortext%TYPE;

/**
* @Desc generate a new version of this package from mam_named_exceptions.
*       EXEC mam_exceptions.generateNamedExceptions(<directory_name>);
*       Replace the code between "/*+*Begin/End of generated code..." with
*       declarations for the named exceptions. Other code will not be changed.
* @param p_pfad Oracle Directorie for output files
*/
PROCEDURE generateNamedExceptions
    (
     p_path             IN all_directories.directory_name%TYPE
    );

END mam_exceptions;

Package created.

Statement 18

CREATE OR REPLACE PACKAGE BODY mam_exceptions
IS

    c_typeHeader            user_source.type%TYPE := 'PACKAGE';
    c_typeBody              user_source.type%TYPE := 'PACKAGE BODY';

    CURSOR cur_source (
         p_type             IN VARCHAR2
        ,p_from             IN user_source.line%TYPE
        ,p_to               IN user_source.line%TYPE
        )
    IS
        SELECT  us.text
        FROM    user_source us
        WHERE   us.type         = p_type
        AND     UPPER(us.name)  = UPPER(c_packageName)
        AND     (  us.line      > p_from
                OR p_from      IS NULL
                )
        AND     (  us.line      < p_to
                OR p_to        IS NULL
                )
        ORDER BY line;

    CURSOR cur_exceptions
    IS
        SELECT  mne.*
        FROM    mam_named_exceptions mne
        WHERE   mne.mne_sqlcode BETWEEN -20999 AND -20000
        ORDER BY mne.mne_sqlcode DESC;

/*+*Begin of generated code. Do not remove this comment!*+*/
FUNCTION getStop
    RETURN INTEGER
IS
BEGIN
    RETURN c_stop;
END;

FUNCTION getOutOfRange
    RETURN INTEGER
IS
BEGIN
    RETURN c_outOfRange;
END;

FUNCTION getNotGapless
    RETURN INTEGER
IS
BEGIN
    RETURN c_notGapless;
END;

/*+*End of generated code. Do not remove this comment!*+*/

FUNCTION decodeText
    (
     p_text             IN VARCHAR2
    )
    RETURN VARCHAR2
IS
    v_values            VARCHAR2(2000);
    v_posTilde          PLS_INTEGER := 0;
    v_posPipe           PLS_INTEGER := 0;
    v_posPipe2          PLS_INTEGER := 0;
    v_length            PLS_INTEGER := 0;
    v_text              VARCHAR2(2000) := p_text;
BEGIN
    IF INSTR(v_text,'|') > 0 THEN
        v_values    := SUBSTR(v_text,INSTR(v_text,'|'));
        v_text      := SUBSTR(v_text, 1, INSTR(v_text, '|') - 1);

        LOOP
            -- get first place marker
            v_posTilde  := INSTR(v_text, '~', v_posTilde + 1);
            --get first value marker
            v_posPipe     := INSTR(v_values, '|', v_posPipe + 1);
            --get second value marker
            v_posPipe2    := INSTR(v_values, '|', v_posPipe + 1);

            IF   v_posTilde = 0
              OR v_posPipe = 0
            THEN
                EXIT;
            ELSIF v_posPipe2 > 0 THEN
                v_length := v_posPipe2 - v_posPipe - 1;
            ELSE
                v_length := LENGTH(v_values) - v_posPipe;
            END IF;
            -- replace place marker with value
            v_text  := SUBSTR(v_text, 1, v_posTilde - 1)||
                       SUBSTR(v_values, v_posPipe + 1, v_length)||
                       SUBSTR(v_text, v_posTilde + 1);
        END LOOP;
    END IF;

    RETURN v_text;

END decodeText;

/**
* @desc raise_application_error with decoding of the error text
*           for unregistered SQLCODEs
* @param p_sqlcode      SQLCODE / mne_sqlcode
* @param p_text         error message
* @param p_keepErrorStack  Keep error stack
*/
PROCEDURE rae
    (
     p_sqlcode          IN mam_named_exceptions.mne_sqlcode%TYPE
    ,p_text             IN mam_named_exceptions.mne_errortext%TYPE
    ,p_keepErrorStack   IN BOOLEAN  := FALSE
    )
IS
    v_backtrace         VARCHAR(500);
BEGIN

    IF isNamedException(p_sqlcode) THEN
        RAISE_APPLICATION_ERROR (
             p_sqlcode
            ,p_text
            ,p_keepErrorStack
            );
    ELSE
        RAISE_APPLICATION_ERROR (
             p_sqlcode
            ,decodeText(p_text)
            ,p_keepErrorStack
            );
    END IF;

END rae;

/**
* @desc Check whether the SQLCODE is a registered exception
* @param p_sqlcode  SQLCODE
* @return TRUE, if SQLCODE in mam_named_exceptions, else FALSE
*/
FUNCTION isNamedException
    (
     p_sqlcode          IN mam_named_exceptions.mne_sqlcode%TYPE
    )
    RETURN BOOLEAN
IS
    v_count             PLS_INTEGER;
BEGIN

    SELECT  COUNT(*)
    INTO    v_count
    FROM    mam_named_exceptions
    WHERE   mne_sqlcode = p_sqlcode
    AND     ROWNUM      < 2;

    RETURN v_count > 0;

END isNamedException;

/**
* @desc get row from mam_named_exceptions for SQLCODE
* @param p_sqlcode SQLCODE
* @return Row from mam_named_exceptions, NULL if SQLCODE is not registered
*/
FUNCTION getNamedException
    (
     p_sqlcode          IN mam_named_exceptions.mne_sqlcode%TYPE
    )
    RETURN mam_named_exceptions%ROWTYPE
IS
    r_mne               mam_named_exceptions%ROWTYPE;
BEGIN

    SELECT  *
    INTO    r_mne
    FROM    mam_named_exceptions
    WHERE   mne_sqlcode  = p_sqlcode;

    RETURN r_mne;

END getNamedException;

/**
* @desc get message text for SQLCODE
*       Replace parameters in message text
* @param p_sqlcode  SQLCODE
* @param p_arg      Parameters for parametrised texts
* @return message text
*/
FUNCTION getMessagetext
    (
     p_sqlcode          IN mam_named_exceptions.mne_sqlcode%TYPE
    ,p_arg              IN mam_named_exceptions.mne_errortext%TYPE := NULL
    )
    RETURN mam_named_exceptions.mne_errortext%TYPE
IS
    v_text              mam_named_exceptions.mne_errortext%TYPE;
BEGIN
    IF isNamedException(p_sqlcode) THEN
        v_text  := getNamedException(p_sqlcode).mne_errortext||SUBSTR(p_arg,INSTR(p_arg,'|'));
    ELSE
        v_text  := p_arg;
    END IF;

    RETURN decodeText(v_text);
END getMessagetext;

/**
* @Desc Search begin and end of generated code
* @param p_typ    Search Header c_typeHeader or Body c_typeBody
* @param p_begin  first line
* @param p_end    last line
*/
PROCEDURE getGeneratedLines
    (
     p_type             IN VARCHAR2
    ,p_begin            OUT user_source.line%TYPE
    ,p_end              OUT user_source.line%TYPE
    )
IS

BEGIN
    SELECT  MIN(us.line)
    INTO    p_begin
    FROM    user_source us
    WHERE   us.type =  p_type
    AND     UPPER(us.name) = UPPER(c_packageName)
    AND     us.text LIKE '/*+*Begin of generated code. Do not remove this comment!*+*%';

    SELECT  MIN(us.line)
    INTO    p_end
    FROM    user_source us
    WHERE   us.type =  p_type
    AND     UPPER(us.name) = UPPER(c_packageName)
    AND     us.text LIKE '/*+*End of generated code. Do not remove this comment!*+*%';

END getGeneratedLines;

/**
* @Desc generate a new version of this package from mam_named_exceptions.
*       EXEC mam_exceptions.generateNamedExceptions(<directory_name>);
*       Replace the code between "/*+*Begin/End of generated code..." with
*       declarations for the named exceptions. Other code will not be changed.
* @param p_pfad Oracle Directorie for output files
*/
PROCEDURE generateNamedExceptions
    (
     p_path             IN all_directories.directory_name%TYPE
    )
IS
    v_from              user_source.line%TYPE;
    v_to                user_source.line%TYPE;
    v_text              user_source.text%TYPE;
    v_fileHandle        utl_file.FILE_TYPE;
    v_firstLine         BOOLEAN := TRUE;
BEGIN
    getGeneratedLines(
         c_typeHeader
        ,v_from
        ,v_to
        );
    v_fileHandle   := utl_file.fopen (p_path, c_packageName||'.pck', 'W', 32760);

    -- Read and write code until begin of generated part
    FOR r_source IN cur_source (c_typeHeader,NULL,v_from + 1) LOOP
        IF v_firstLine THEN
            utl_file.put_line(v_fileHandle,'CREATE OR REPLACE '||RTRIM(r_source.text,CHR(10)));
            v_firstLine    := FALSE;
        ELSE
            utl_file.put_line(v_fileHandle,RTRIM(r_source.text,CHR(10)));
        END IF;
    END LOOP;

    -- Define Exceptions and register as Named Exceptions
    FOR r_exceptions IN cur_exceptions LOOP
        utl_file.put_line(v_fileHandle,'    e_' || SUBSTR(r_exceptions.mne_errorname,1,28) || ' EXCEPTION;');
        utl_file.put_line(v_fileHandle,'    c_' || SUBSTR(r_exceptions.mne_errorname,1,28) || ' CONSTANT INTEGER := ' || r_exceptions.mne_sqlcode || ';');
        utl_file.put_line(v_fileHandle,'    PRAGMA EXCEPTION_INIT (e_' ||SUBSTR(r_exceptions.mne_errorname,1,28) || ', ' || r_exceptions.mne_sqlcode || ');');
        utl_file.put_line(v_fileHandle,'');
    END LOOP;

    -- Functions to read SQL-CODES, e.g. from Forms.
    FOR r_exceptions IN cur_exceptions LOOP
        utl_file.put_line(v_fileHandle,'FUNCTION get' || UPPER(SUBSTR(r_exceptions.mne_errorname,1,1))||SUBSTR(r_exceptions.mne_errorname,2,26));
        utl_file.put_line(v_fileHandle,'    RETURN INTEGER;');
        utl_file.put_line(v_fileHandle,'');
    END LOOP;

    -- Read and write code from end of generated part
    FOR r_source IN cur_source (c_typeHeader,v_to - 1,NULL) LOOP
        utl_file.put_line(v_fileHandle,RTRIM(r_source.text,CHR(10)));
    END LOOP;

    utl_file.put_line(v_fileHandle,'/');
    utl_file.put_line(v_fileHandle,'');
    utl_file.put_line(v_fileHandle,'SHOW ERRORS');
    utl_file.fclose(v_fileHandle);

    getGeneratedLines(
         c_typeBody
        ,v_from
        ,v_to
        );
    v_fileHandle   := utl_file.fopen(p_path, c_packageName||'.pcb', 'W', 32760);

    -- Read and write code until begin of generated part
    v_firstLine    := TRUE;
    FOR r_source IN cur_source (c_typeBody,NULL,v_from + 1) LOOP
        IF v_firstLine THEN
            utl_file.put_line(v_fileHandle,'CREATE OR REPLACE '||RTRIM(r_source.text,CHR(10)));
            v_firstLine    := FALSE;
        ELSE
            utl_file.put_line(v_fileHandle,RTRIM(r_source.text,CHR(10)));
        END IF;
    END LOOP;

    -- Functions to read SQL-CODES, e.g. from Forms.
    FOR r_exceptions IN cur_exceptions LOOP
        utl_file.put_line(v_fileHandle,'FUNCTION get' || UPPER(SUBSTR(r_exceptions.mne_errorname,1,1))||SUBSTR(r_exceptions.mne_errorname,2,26));
        utl_file.put_line(v_fileHandle,'    RETURN INTEGER');
        utl_file.put_line(v_fileHandle,'IS');
        utl_file.put_line(v_fileHandle,'BEGIN');
        utl_file.put_line(v_fileHandle,'    RETURN c_' || SUBSTR(r_exceptions.mne_errorname,1,28) || ';');
        utl_file.put_line(v_fileHandle,'END;');
        utl_file.put_line(v_fileHandle,'');
    END LOOP;

    -- Read and write code from end of generated part
    FOR r_source IN cur_source (c_typeBody,v_to - 1,NULL) LOOP
        utl_file.put_line(v_fileHandle,RTRIM(r_source.text,CHR(10)));
    END LOOP;

    utl_file.put_line(v_fileHandle,'/');
    utl_file.put_line(v_fileHandle,'');
    utl_file.put_line(v_fileHandle,'SHOW ERRORS');
    utl_file.fclose(v_fileHandle);

END generateNamedExceptions;

END mam_exceptions;

Package Body created.

Statement 19

SET SERVEROUTPUT ON

Unsupported Command

Statement 20

BEGIN
	dbms_output.put_line(mam_exceptions.getMessagetext(-20502,NULL));
	dbms_output.put_line(mam_exceptions.getMessagetext(-20501));
	dbms_output.put_line(mam_exceptions.getMessagetext(-20501,'|param1|1|2|3'));
	dbms_output.put_line(mam_exceptions.getMessagetext(0,'Text with replace ~ here|text'));
END;

The intervals must have no gaps.
The value of ~ is out of range. Expected ~ to ~, is ~.
The value of param1 is out of range. Expected 1 to 2, is 3.
Text with replace text here

Statement 21

BEGIN
	RAISE mam_exceptions.e_stop;
EXCEPTION
	WHEN mam_exceptions.e_stop THEN
		dbms_output.put_line(mam_exceptions.getMessagetext(mam_exceptions.c_stop));
END;

Termination of the program execution. See log messages

Statement 22

BEGIN
	mam_exceptions.rae(mam_exceptions.c_outOfRange,'|param1|1|2|3');
EXCEPTION
	WHEN mam_exceptions.e_outOfRange THEN
		dbms_output.put_line(mam_exceptions.getMessagetext(SQLCODE,SQLERRM));
END;

The value of param1 is out of range. Expected 1 to 2, is 3.

Generate Named Exceptions

Additional Information