This is a translation reference to convert the Oracle Record Declaration to Snowflake
This section is a work in progress, information may change in the future.
Some parts in the output code are omitted for clarity reasons.
Description
A record variable is a composite variable whose internal components, called fields, can have different data types. The value of a record variable and the values of its fields can change.
You reference an entire record variable by its name. You reference a record field with the syntax record.field.
You can create a record variable in any of these ways:
Define a record type and then declare a variable of that type.
Use %ROWTYPE to declare a record variable that represents either a full or partial row of a database table or view.
Use %TYPE to declare a record variable of the same type as a previously declared record variable.
For the translation, the type definition is replaced by an OBJECT Semi-structured Data Type and then its usages are changed accordingly across any operations.
In order to define a Record type, the syntax is as follows:
Some parts in the output code are omitted for clarity reasons.
Record initialization and assignment
This sample attempts to insert two new rows using a record variable which is reassigned mid-procedure.
Oracle
IN -> Oracle_01.sql
CREATETABLErecord_table(col1 FLOAT, col2 INTEGER);CREATEORREPLACEPROCEDURE record_procedureISTYPE record_typ IS RECORD(col1 INTEGER, col2 FLOAT); record_variable record_typ := record_typ(1, 1.5);--initializationBEGININSERT INTO record_table(col1, col2)VALUES (record_variable.col2, record_variable.col1);--usage--reassignment of properties record_variable.col1 :=2; record_variable.col2 :=2.5;INSERT INTO record_table(col1, col2)VALUES (record_variable.col2, record_variable.col1);--usageEND;CALL record_procedure();SELECT*FROM record_table;
|COL1|COL2|
|----+----|
| 1.5| 1|
| 2.5| 2|
Snowflake
Notice how the reassignments are replaced by an OBJECT_INSERT that updates if the column already exists, and how the VALUES clause is replaced by a SELECT.
OUT -> Oracle_01.sql
CREATE OR REPLACETABLErecord_table (col1 FLOAT, col2 INTEGER)COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;CREATEORREPLACEPROCEDURE record_procedure ()RETURNSVARCHARLANGUAGESQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTEASCALLERAS$$DECLARE !!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT ***/!!!
TYPE record_typ IS RECORD(col1 INTEGER, col2 FLOAT); record_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - record_typ DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT('COL1', 1, 'COL2', 1.5);--initialization
BEGININSERT INTO record_table(col1, col2)SELECT :record_variable:COL2, :record_variable:COL1;--usage--reassignment of properties record_variable := OBJECT_INSERT(record_variable, 'COL1', 2, true); record_variable := OBJECT_INSERT(record_variable, 'COL2', 2.5, true);INSERT INTO record_table(col1, col2)SELECT :record_variable:COL2, :record_variable:COL1;--usageEND;$$;CALL record_procedure();SELECT*FROM record_table;
COL1|COL2|
----+----+
1.5| 1|
2.5| 2|
%ROWTYPE Record and Values Record
Since the operations are the ones that define the structure, these definitions can be replaced by an OBJECT datatype, but the values of the record need to be decomposed as inserting the record "as-is" is not supported.
Please note the additional OBJECT_CONSTRUCT in the Cursor definition, this is what allows to extract an OBJECT, which then can be used to seamlessly migrate the FETCH statement.
OUT -> Oracle_03.sql
CREATE OR REPLACETABLErecord_table (col1 INTEGER, col2 VARCHAR(50), col3 TIMESTAMP)COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;INSERT INTO record_table(col1, col2 , col3)VALUES (1, 'Hello', DATE'2020-12-25');CREATEORREPLACEPROCEDURE load_cursor_record ()RETURNSVARCHARLANGUAGESQLCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTEASCALLERAS$$DECLARE record_cursor CURSORFORSELECT OBJECT_CONSTRUCT( *) sc_cursor_recordFROM record_table; record_variable OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
BEGINOPEN record_cursor;LOOP --** SSC-PRF-0003 - FETCH INSIDE A LOOP IS CONSIDERED A COMPLEX PATTERN, THIS COULD DEGRADE SNOWFLAKE PERFORMANCE. **
FETCH record_cursor INTO :record_variable;IF (record_variable ISNULL) THEN EXIT;ENDIF;--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **CALL DBMS_OUTPUT.PUT_LINE_UDF(:record_variable:COL1);--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **CALL DBMS_OUTPUT.PUT_LINE_UDF(:record_variable:COL2);--** SSC-FDM-OR0035 - CHECK UDF IMPLEMENTATION FOR DBMS_OUTPUT.PUT_LINE_UDF. **CALL DBMS_OUTPUT.PUT_LINE_UDF(:record_variable:COL3::DATE);ENDLOOP;CLOSE record_cursor;END;$$;CALL load_cursor_record();
DBMS OUTPUT
-----------
1
Hello
25-DEC-20
Assigning a Record Variable in a SELECT INTO
This transformation consists in taking advantage of the OBJECT_CONTRUCT function to initialize the record using the SELECT columns as the arguments.
Sample auxiliary code
IN -> Oracle_04.sql
createtablesample_table(ID number, NAMEvarchar2(23));CREATETABLERESULTS (COL1 VARCHAR(20), COL2 VARCHAR(40));insert into sample_table values(1, 'NAME 1');insert into sample_table values(2, 'NAME 2');insert into sample_table values(3, 'NAME 3');insert into sample_table values(4, 'NAME 4');
OUT -> Oracle_04.sql
CREATE OR REPLACE TABLE sample_table (ID NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
NAMEVARCHAR(23))COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;CREATE OR REPLACETABLERESULTS (COL1 VARCHAR(20),COL2 VARCHAR(40))COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;insert into sample_tablevalues(1, 'NAME 1');insert into sample_tablevalues(2, 'NAME 2');insert into sample_tablevalues(3, 'NAME 3');insert into sample_tablevalues(4, 'NAME 4');
Oracle
IN -> Oracle_05.sql
CREATEORREPLACEPROCEDURE sp_sample1 AS-- Rowtype variablerowtype_variable sample_table%rowtype;--Record variableTYPE record_typ_def IS RECORD(ID number, NAMEvarchar2(23));record_variable_def record_typ_def;-- Auxiliary variablename_var VARCHAR(20);BEGINSELECT*INTO rowtype_variable FROM sample_table WHERE ID =1FETCHNEXT1ROWS ONLY; name_var := rowtype_variable.NAME;INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 1', name_var);SELECT ID, NAMEINTO rowtype_variable FROM sample_table WHERE ID =2FETCHNEXT1ROWS ONLY; name_var := rowtype_variable.NAME;INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 2', name_var);SELECT*INTO record_variable_def FROM sample_table WHERE ID =3FETCHNEXT1ROWS ONLY; name_var := record_variable_def.NAME;INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 3', name_var);SELECT ID, NAMEINTO record_variable_def FROM sample_table WHERE ID =4FETCHNEXT1ROWS ONLY; name_var := record_variable_def.NAME;INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 4', name_var);END;call sp_sample1();SELECT*FROM results;