MSCEWI3051

PRAGMA EXCEPTION_INIT is not supported.

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

This warning is added when PRAGMA EXCEPTION_INIT function is invoked within a procedure. When the procedure is transformed to JavaScript, the Exception Name and SQL Code of the exceptions are set in the RAISE function. When it is converted to Snowflake Scripting, the SQL Code is added to the Exception declaration, however, some code values may be invalid in Snowflake Scripting.

Example Code

Input Code:

CREATE OR REPLACE PROCEDURE EXCEPTION_DECLARATION_SAMPLE AUTHID DEFINER IS
  NEW_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT(NEW_EXCEPTION, -63);
  NEW_EXCEPTION2 EXCEPTION;
  PRAGMA EXCEPTION_INIT ( NEW_EXCEPTION2, -20100 );
BEGIN

  IF true THEN
    RAISE NEW_EXCEPTION;
  END IF;

EXCEPTION
    WHEN NEW_EXCEPTION THEN
        --Handle Exceptions
        NULL;
END;
/

Output Code:

JavaScript

--Basic example with raise
CREATE OR REPLACE PROCEDURE PUBLIC.EXCEPTION_DECLARATION_SAMPLE ()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
   //Helpers declaration
   var RAISE = function (code,name,message) {
      var error = new Error(message);
      Object.assign(error,{
         code : code,
         name : name,
         message : message
      })
      SQLCODE = error.code
      SQLERRM = `${error.code}: ${message}`
      throw error;
   };
   {
   /* ** MSC-WARNING - MSCEWI3052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ** */
   /*   NEW_EXCEPTION EXCEPTION */
   ;
   /* ** MSC-WARNING - MSCEWI3051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ** */
   /*   PRAGMA EXCEPTION_INIT(NEW_EXCEPTION, -63) */
   ;
   /* ** MSC-WARNING - MSCEWI3052 - EXCEPTION DECLARATION IS HANDLED BY RAISE FUNCTION ** */
   /*   NEW_EXCEPTION2 EXCEPTION */
   ;
   /* ** MSC-WARNING - MSCEWI3051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED ** */
   /*   PRAGMA EXCEPTION_INIT ( NEW_EXCEPTION2, -20100 ) */
   ;
      try {
         if (true) {
            RAISE(63,"NEW_EXCEPTION","NEW_EXCEPTION");
         }
      } catch(error) {
         switch(error.name) {
            case `NEW_EXCEPTION`: {
               //Handle Exception
               null;
               break;
            }
            default: {
               throw error;
               break;
            }
         }
      }
   }
$$;

Snowflake Scripting

CREATE OR REPLACE PROCEDURE PUBLIC.EXCEPTION_DECLARATION_SAMPLE ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
/*** MSC-WARNING - MSCEWI3097 - PROCEDURE PROPERTIES ARE NOT SUPPORTED IN SNOWFLAKE PROCEDURES ***/
AS
$$
   DECLARE
      NEW_EXCEPTION EXCEPTION /*** MSC-WARNING - MSCEWI3099 - EXCEPTION CODE NUMBER EXCEEDS SNOWFLAKE SCRIPTING LIMITS ***/;
-- ** MSC-WARNING - MSCEWI3051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED **
--      PRAGMA EXCEPTION_INIT(NEW_EXCEPTION, -63)
                                               ;
      NEW_EXCEPTION2 EXCEPTION (-20100, '');
-- ** MSC-WARNING - MSCEWI3051 - PRAGMA EXCEPTION_INIT IS NOT SUPPORTED **
--      PRAGMA EXCEPTION_INIT ( NEW_EXCEPTION2, -20100 )
                                                      ;
   BEGIN
   -- Procedure statements...
   END;
$$;

Recommendations

• No end-user action is required.

• If you need more support, you can email us at [email protected]