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).
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.
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE TEST( SAMPLE_A NUMBER(38, 18) DEFAULTNULL, SAMPLE_B NUMBER(38, 18) DEFAULTNULL)RETURNSNUMBER(38, 18)LANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTEASCALLERAS$$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;RETURN1;END;$$;
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.
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE TEST( SAMPLE_A NUMBER(38, 18) DEFAULTNULL, SAMPLE_B NUMBER(38, 18) DEFAULTNULL)RETURNSNUMBER(38, 18)LANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTEASCALLERAS$$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;RETURN1;END;$$;
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
IN -> Oracle_04.sql
CREATE OR REPLACEFUNCTIONTEST( SAMPLE_A INNUMBERDEFAULTNULL, SAMPLE_B INNUMBERDEFAULTNULL)RETURNNUMBERASBEGINIF TRUE THEN raise_application_error(-20001, 'The first exception');ELSE raise_application_error(-20001, 'Other exception inside');ENDIF;RETURN1;END TEST;
ORA-20000: The first exception
Snowflake
OUT -> Oracle_04.sql
--** SSC-FDM-0029 - USER DEFINED FUNCTION WAS TRANSFORMED TO SNOWFLAKE PROCEDURE **CREATEORREPLACEPROCEDURE TEST( SAMPLE_A NUMBER(38, 18) DEFAULTNULL, SAMPLE_B NUMBER(38, 18) DEFAULTNULL)RETURNSNUMBER(38, 18)LANGUAGESQLCOMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'EXECUTEASCALLERAS$$DECLARE THE_FIRST_EXCEPTION_EXCEPTION_CODE_0 EXCEPTION (-20001, 'THE FIRST EXCEPTION'); OTHER_EXCEPTION_INSIDE_EXCEPTION_CODE_1 EXCEPTION (-20001, 'OTHER EXCEPTION INSIDE');BEGINIF (TRUE) THEN RAISE THE_FIRST_EXCEPTION_EXCEPTION_CODE_0;ELSE RAISE OTHER_EXCEPTION_INSIDE_EXCEPTION_CODE_1;ENDIF;RETURN1;END;$$;
THE FIRST EXCEPTION
Known Issues
SQLREM function may be reviewed.
Exception code number outside the applicable limits in Snowflake has to be changed to an available code exception.
Add to a stack of errors is not supported.
Related EWIs
SSC-EWI-OR0099: The exception code exceeds the Snowflake Scripting limit.
SSC-FDM-0029: User defined function was transformed to a Snowflake procedure.
SSC-FDM-OR0011: The boolean argument was removed because the "add to stack" options is not supported.