Oracle

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

Oracle Unsupported Cases

1. Unsupported types

Custom types declarations are not supported. Variable declarations using custom types are being transformed to VARIANT even though, all the usages of those variables are also marked as not supported.

Some of these cases are going to be supported in the future by using Snowflake arrays and objects.

Input Code:

CREATE OR REPLACE PROCEDURE unsupported_types (FLAG NUMBER)
IS

PRAGMA AUTONOMOUS_TRANSACTION;
TYPE ASSOC_ARRAY_TYPE IS TABLE OF ftable4%ROWTYPE INDEX BY PLS_INTEGER;
TYPE CUSTOM_TYPE1 IS TABLE OF NUMBER(10);
TYPE CUSTOM_TYPE2 IS VARRAY(1) OF NUMBER NOT NULL;
TYPE CUSTOM_TYPE3 IS VARYING ARRAY(1) OF NUMBER;

TYPE NUMBER_RECORD_DEFINITION IS RECORD(
      rec_id f_employee.id%TYPE,
      rec_name f_employee.first_name%TYPE,
	  rec_collection CUSTOM_TYPE1
    );

number_record NUMBER_RECORD_DEFINITION;
custom_type1_var CUSTOM_TYPE1;
custom_type2_var CUSTOM_TYPE2;
custom_type3_var CUSTOM_TYPE3;

BEGIN
    SELECT id, first_name INTO number_record FROM f_employee WHERE id = 1 FETCH NEXT 1 ROWS ONLY;
END unsupported_types;

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.unsupported_types (FLAG NUMBER (38,18))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE

-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'PL PRAGMA ITEM MEMBER' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      PRAGMA AUTONOMOUS_TRANSACTION;
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEF' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      TYPE ASSOC_ARRAY_TYPE IS TABLE OF ftable4%ROWTYPE INDEX BY PLS_INTEGER;
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEF' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      TYPE CUSTOM_TYPE1 IS TABLE OF NUMBER(10);
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEF' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      TYPE CUSTOM_TYPE2 IS VARRAY(1) OF NUMBER NOT NULL;
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEF' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      TYPE CUSTOM_TYPE3 IS VARYING ARRAY(1) OF NUMBER;

-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'PL RECORD TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      TYPE NUMBER_RECORD_DEFINITION IS RECORD(
--            rec_id f_employee.id%TYPE,
--            rec_name f_employee.first_name%TYPE,
--      	  rec_collection CUSTOM_TYPE1
--          );

      number_record VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'NUMBER_RECORD_DEFINITION' USAGE CHANGED TO VARIANT ***/;
      custom_type1_var VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'CUSTOM_TYPE1' USAGE CHANGED TO VARIANT ***/;
      custom_type2_var VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'CUSTOM_TYPE2' USAGE CHANGED TO VARIANT ***/;
      custom_type3_var VARIANT /*** MSC-WARNING - MSCEWI1062 - CUSTOM TYPE 'CUSTOM_TYPE3' USAGE CHANGED TO VARIANT ***/;
   BEGIN
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'RECORDS AND COLLECTIONS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      SELECT id, first_name INTO :number_record FROM PUBLIC.f_employee WHERE id = 1
--      FETCH NEXT 1 ROWS ONLY
                            ;
   END;
$$;

2. Fetch Bulk Collect Into

Snowflake Scripting does not support the BULK COLLECT INTO clause. For this reason, results will need to be passed through other ways.

Input Code:

CREATE OR REPLACE PROCEDURE cursor_unsupported (FLAG NUMBER)
IS
CURSOR c1 IS SELECT * FROM f_employee;
update_record f_employee%rowtype;

BEG
    FETCH c1 BULK COLLECT INTO col1;
END cursor_unsupported;

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.cursor_unsupported (FLAG NUMBER (38,18))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      c1 CURSOR FOR SELECT * FROM PUBLIC.f_employee;
      update_record VARIANT;
   BEGIN
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'FETCH BULK COLLECT INTO' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      FETCH c1 BULK COLLECT INTO col1
                                     ;
   END;
$$;

3. DML statement unsupported patterns

Some DML clauses or usages of INSERT, UPDATE and DELETE are not supported when combined with cursors or records.

Input Code:

CREATE OR REPLACE PROCEDURE dml_unsupported (FLAG NUMBER)
IS
CURSOR c1 IS SELECT * FROM f_employee;
update_record f_employee%rowtype;
BEGIN
   FOR CREC IN C1 LOOP
	  IF crec.id = 6 THEN
	   	DELETE FROM f_employee WHERE CURRENT OF c1;
	   	EXIT;
	  END IF;
	END LOOP;

    FOR CREC IN C1 LOOP
		IF crec.id = 4 THEN
	   	UPDATE f_employee SET ROW = update_record WHERE CURRENT OF c1;
	  END	IF;
	END LOOP;

    INSERT INTO f_employee VALUES update_record;

END dml_unsupported;

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.dml_unsupported (FLAG NUMBER (38,18))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      c1 CURSOR FOR SELECT * FROM PUBLIC.f_employee;
      update_record VARIANT;
   BEGIN
      FOR CREC IN C1 DO
         IF ( crec.id = 6) THEN
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'CURRENT OF' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--            DELETE FROM PUBLIC.f_employee WHERE CURRENT OF c1
                                                              ;
            EXIT;
         END IF;
      END FOR;
      FOR CREC IN C1 DO
         IF ( crec.id = 4) THEN
-- ** MSC-ERROR - MSCEWI1075 - CURRENT OF CLAUSE IS NOT SUPPORTED IN SNOWFLAKE **
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'UPDATE ROW RECORD' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--            UPDATE f_employee SET ROW = update_record WHERE CURRENT OF c1
                                                                         ;
         END IF;
      END FOR;

-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'VALUES RECORD' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--      INSERT INTO PUBLIC.f_employee VALUES update_record
                                                        ;
   END;
$$;

4. Execute Immediate not supported patterns

Snowflake Execute Immediate does not support returning a value and storing it inside a variable using INTO syntax. Also, another unsupported case is when IN/OUT is used along with the USING clause.

Input Code:

CREATE OR REPLACE PROCEDURE execute_immediate_unsupported (FLAG NUMBER)
IS
BEGIN
    EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL' INTO number_variable;
    EXECUTE IMMEDIATE 'SELECT 1 FROM T1 where T1.COL1 = :a' USING IN OUT number_variable;
END execute_immediate_unsupported;

Output Code:

CREATE OR REPLACE PROCEDURE PUBLIC.execute_immediate_unsupported (FLAG NUMBER (38,18))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   BEGIN
      EXECUTE IMMEDIATE 'SELECT 1 FROM DUAL'
-- ** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'EXECUTE IMMEDIATE RETURNING CLAUSE' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--                                             INTO number_variable
                                                                 ;
      EXECUTE IMMEDIATE 'SELECT 1 FROM PUBLIC.T1 where T1.COL1 = ?' USING ( /*** MSC-ERROR - MSCEWI1058 - FUNCTIONALITY FOR 'IN/OUT MODES FOR USING CLAUSE BIND PARAMETERS' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/ /*IN*/ /*OUT*/ number_variable);
   END;
$$;

5. Other unsupported cases

For most of the unsupported cases or important considerations, there are other specific MSCEWIs that can be checked here:

Code

Description

PreviousTeradata NextSQL Server

Last updated 7 months ago