RAISE_APPICATION_ERROR

Translation reference for the raise_application_error statement.

General description

The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions (Oracle documentation).

Oracle syntax

raise_application_error(
      error_number, message[, {TRUE | FALSE}]);

The error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long.

If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors.

The equivalent statement in Snowflake is the RAISE clause, nevertheless, it is required to declare the user-defined exception as a variable before calling the RAISE statement for it.

Snowflake Syntax

<exception_name> EXCEPTION [ ( <exception_number> , '<exception_message>' ) ] ;

For more information review the following Snowflake documentation.

Sample Source Patterns

1. Exception in functions without declaring section

In this scenario, the function without a declaring section is translated to a procedure with the exception declaration. Please note that:

• The exception variable name is declared in upper case.

• The exception variable name is based on the description and an ending is composed of an exception code name followed by a consecutive number.

• The declaring section is created even though the initial function or procedure does not contain it.

Oracle

Query

IN -> Oracle_01.sql

CREATE OR REPLACE FUNCTION TEST(
    SAMPLE_A IN NUMBER DEFAULT NULL,
    SAMPLE_B IN NUMBER DEFAULT NULL
)
RETURN NUMBER
AS
BEGIN
    raise_application_error(-20001, 'First exception message', FALSE);
    raise_application_error(-20002, 'Second exception message');
  RETURN 1;
END TEST;

Output

ORA-20001: First exception message

Snowflake

Query

OUT -> Oracle_01.sql

--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE TEST(
   SAMPLE_A NUMBER(38, 18) DEFAULT NULL,
   SAMPLE_B NUMBER(38, 18) DEFAULT NULL
)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
  DECLARE
    FIRST_EXCEPTION_MESSAGE_EXCEPTION_CODE_0 EXCEPTION (-20001, 'FIRST EXCEPTION MESSAGE');
    SECOND_EXCEPTION_MESSAGE_EXCEPTION_CODE_1 EXCEPTION (-20002, 'SECOND EXCEPTION MESSAGE');
  BEGIN
    --** SSC-FDM-OR0011 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT FALSE WAS REMOVED. **
    RAISE FIRST_EXCEPTION_MESSAGE_EXCEPTION_CODE_0;
    RAISE SECOND_EXCEPTION_MESSAGE_EXCEPTION_CODE_1;
    RETURN 1;
  END;
$$;

Output

FIRST EXCEPTION MESSAGE

2. Exception code number outside limits

The following example shows the translation commented out in the procedure body. It is because the code is outside the applicable code limits in Snowflake. The solution is to change the exception code for an available code in the query section.

Oracle

Query

IN -> Oracle_02.sql

CREATE OR REPLACE FUNCTION TEST(
    SAMPLE_A IN NUMBER DEFAULT NULL,
    SAMPLE_B IN NUMBER DEFAULT NULL
)
RETURN NUMBER
AS
BEGIN
    raise_application_error(-20000, 'My exception message');
    RETURN 1;
END TEST;

Output

ORA-20000: My exception message

Snowflake

Query

OUT -> Oracle_02.sql

--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE TEST(
   SAMPLE_A NUMBER(38, 18) DEFAULT NULL,
   SAMPLE_B NUMBER(38, 18) DEFAULT NULL
)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_EXCEPTION_MESSAGE_EXCEPTION_CODE_0 EXCEPTION (-20000, 'MY EXCEPTION MESSAGE');
    BEGIN
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0099 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS ***/!!!
        RAISE MY_EXCEPTION_MESSAGE_EXCEPTION_CODE_0;
        RETURN 1;
    END;
$$;

Output

 Invalid error code '-20,000'. Must be between -20,999 and -20,000

3. Exception stack functionality

The exception stack functionality is not supported in Snowflake and is removed from the exception declaration.

Oracle

Query

IN -> Oracle_03.sql

CREATE OR REPLACE FUNCTION TEST(
    SAMPLE_A IN NUMBER DEFAULT NULL,
    SAMPLE_B IN NUMBER DEFAULT NULL
)
RETURN NUMBER
AS
BEGIN
    raise_application_error(-20001, 'My exception message', TRUE);
    RETURN 1;
END TEST;

Output

ORA-20001: My exception message

Snowflake

Query

OUT -> Oracle_03.sql

--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE TEST(
   SAMPLE_A NUMBER(38, 18) DEFAULT NULL,
   SAMPLE_B NUMBER(38, 18) DEFAULT NULL
)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        MY_EXCEPTION_MESSAGE_EXCEPTION_CODE_0 EXCEPTION (-20001, 'MY EXCEPTION MESSAGE');
    BEGIN
        --** SSC-FDM-OR0011 - ADD TO STACK OF ERRORS IS NOT SUPPORTED, BOOLEAN ARGUMENT TRUE WAS REMOVED. **
        RAISE MY_EXCEPTION_MESSAGE_EXCEPTION_CODE_0;
        RETURN 1;
    END;
$$;

Output

MY EXCEPTION MESSAGE

4. Multiple exceptions with the same exception code

Multiple exceptions with the same can coexist in the declaring section and raise statements.

Oracle

Query

IN -> Oracle_04.sql

CREATE OR REPLACE FUNCTION TEST(
    SAMPLE_A IN NUMBER DEFAULT NULL,
    SAMPLE_B IN NUMBER DEFAULT NULL
)
RETURN NUMBER
AS
BEGIN
    IF TRUE THEN 
        raise_application_error(-20001, 'The first exception');
    ELSE 
        raise_application_error(-20001, 'Other exception inside');
    END IF;
    RETURN 1;
END TEST;

Output

ORA-20000: The first exception

Snowflake

Query

OUT -> Oracle_04.sql

--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **
CREATE OR REPLACE PROCEDURE TEST(
   SAMPLE_A NUMBER(38, 18) DEFAULT NULL,
   SAMPLE_B NUMBER(38, 18) DEFAULT NULL
)
RETURNS NUMBER(38, 18)
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    DECLARE
        THE_FIRST_EXCEPTION_EXCEPTION_CODE_0 EXCEPTION (-20001, 'THE FIRST EXCEPTION');
        OTHER_EXCEPTION_INSIDE_EXCEPTION_CODE_1 EXCEPTION (-20001, 'OTHER EXCEPTION INSIDE');
    BEGIN
        IF (TRUE) THEN
            RAISE THE_FIRST_EXCEPTION_EXCEPTION_CODE_0;
            ELSE
            RAISE OTHER_EXCEPTION_INSIDE_EXCEPTION_CODE_1;
            END IF;
            RETURN 1;
    END;
$$;

Output

THE FIRST EXCEPTION

Known Issues

1. SQLREM function may be reviewed.

2. Exception code number outside the applicable limits in Snowflake has to be changed to an available code exception.

3. Add to a stack of errors is not supported.

Related EWIs

1. SSC-EWI-OR0099: The exception code exceeds the Snowflake Scripting limit.
2. SSC-FDM-0029: User defined function was transformed to a Snowflake procedure.
3. SSC-FDM-OR0011: The boolean argument was removed because the "add to stack" options is not supported.