Record Type Definition

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.

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.

(Oracle PL/SQL Language Reference RECORD VARIABLES)

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:

type_definition := TYPE IS RECORD ( field_definition [, field_definition...] );

field_definition := field_name datatype [ { [NOT NULL default ] | default } ]

default := [ { := | DEFAULT } expression]

To declare a variable of this type:

variable_name { record_type
              | rowtype_attribute
              | record_variable%TYPE
              };

Sample Source Patterns

Record initialization and assignment

This sample attempts to insert two new rows using a record variable which is reassigned mid-procedure.

Oracle

Query

CREATE TABLE record_table(col1 FLOAT, col2 INTEGER);

CREATE OR REPLACE PROCEDURE record_procedure
IS
    TYPE record_typ IS RECORD(col1 INTEGER, col2 FLOAT);
    record_variable record_typ := record_typ(1, 1.5);--initialization
BEGIN
    INSERT 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);--usage
END;

CALL record_procedure();
SELECT * FROM record_table;

Result

|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.

Query

CREATE OR REPLACE TABLE PUBLIC.record_table (
    col1 FLOAT,
    col2 INTEGER
);

CREATE OR REPLACE PROCEDURE PUBLIC.record_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
-- ** MSC-WARNING - MSCEWI1056 - 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 := OBJECT_CONSTRUCT('col1', 1, 'col2', 1.5);--initialization
        --initialization

    BEGIN
        INSERT INTO PUBLIC.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 PUBLIC.record_table(col1, col2)
        SELECT
            :record_variable:col2,
            :record_variable:col1;--usage

    END;
$$;

CALL PUBLIC.record_procedure();

SELECT * FROM
    PUBLIC.record_table;

Result

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.

Oracle

Query

CREATE TABLE record_table(col1 INTEGER, col2 VARCHAR2(50), col3 DATE);
CREATE OR REPLACE PROCEDURE insert_record
IS
    record_variable record_table%ROWTYPE;
BEGIN
    record_variable.col1 := 1;
    record_variable.col2 := 'Hello';
    record_variable.col3 := DATE '2020-12-25';
    
    INSERT INTO record_table VALUES record_variable;
END;

CALL insert_record();
SELECT * FROM record_table;

Result

|COL1|   COL2|     COL3|
|----+-------|---------|
|   1|"Hello"|25-DEC-20|

Snowflake

Please note finally how the OBJECT variable needs to be initialized in order to add the information to it.

Query

CREATE OR REPLACE TABLE PUBLIC.record_table (
    col1 INTEGER,
    col2 VARCHAR(50),
    col3 DATE
);

CREATE OR REPLACE PROCEDURE PUBLIC.insert_record ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
    DECLARE
        record_variable OBJECT := OBJECT_CONSTRUCT();
    BEGIN
        record_variable := OBJECT_INSERT(record_variable, 'col1', 1, true);
        record_variable := OBJECT_INSERT(record_variable, 'col2', 'Hello', true);
        record_variable := OBJECT_INSERT(record_variable, 'col3', DATE '2020-12-25', true);
        INSERT INTO PUBLIC.record_table
        SELECT
            :record_variable:col1,
            : record_variable:col2,
            : record_variable:col3;
    END;
$$;

CALL PUBLIC.insert_record();

SELECT * FROM
    PUBLIC.record_table;

Result

|COL1|   COL2|     COL3|
|----+-------|---------|
|   1|"Hello"|25-DEC-20|

Fetching data into a Record

Oracle

Query

CREATE TABLE record_table(col1 INTEGER, col2 VARCHAR2(50), col3 DATE);
INSERT INTO record_table(col1, col2 , col3)
    VALUES (1, 'Hello', DATE '2020-12-25');

CREATE OR REPLACE PROCEDURE load_cursor_record
IS
    CURSOR record_cursor IS
        SELECT *
        FROM record_table;
        
    record_variable record_cursor%ROWTYPE;
BEGIN
    OPEN record_cursor;
    LOOP
        FETCH record_cursor INTO record_variable;
        EXIT WHEN record_cursor%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(record_variable.col1);
        DBMS_OUTPUT.PUT_LINE(record_variable.col2);
        DBMS_OUTPUT.PUT_LINE(record_variable.col3);
    END LOOP;
    CLOSE record_cursor;
END;

CALL load_cursor_record();

Result

DBMS OUTPUT
-----------
1
Hello
25-DEC-20

Snowflake

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.

Query

CREATE OR REPLACE TABLE record_table(col1 INTEGER, col2 VARCHAR2(50), col3 DATE);

INSERT INTO PUBLIC.record_table(col1, col2 , col3) VALUES (1, 'Hello', DATE '2020-12-25');

CREATE OR REPLACE PROCEDURE PUBLIC.load_cursor_record ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      record_cursor CURSOR FOR
      SELECT OBJECT_CONSTRUCT(*)
      FROM PUBLIC.record_table;

      record_variable OBJECT;
   BEGIN
      OPEN record_cursor;
      LOOP
         FETCH record_cursor INTO record_variable;
         IF (record_variable IS NULL) THEN
            EXIT ;
         END IF;
         
         CALL DBMS_OUTPUT.PUT_LINE(:record_variable:COL1);--columns are case_sensitive
         CALL DBMS_OUTPUT.PUT_LINE(:record_variable:COL2);
         CALL DBMS_OUTPUT.PUT_LINE(:record_variable:COL3);
      END LOOP;
      CLOSE record_cursor;
   END;
$$;

CALL PUBLIC.load_cursor_record();

SELECT * FROM DBMS_OUTPUT.DBMS_OUTPUT_LOG;

Result

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

Oracle

create table sample_table(ID number, NAME varchar2(23));
CREATE TABLE RESULTS (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');

Snowflake

CREATE OR REPLACE TABLE PUBLIC.sample_table (
   ID NUMBER(38, 18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
   NAME VARCHAR(23) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/
);

CREATE OR REPLACE TABLE PUBLIC.RESULTS (
   COL1 VARCHAR(20),
   COL2 VARCHAR(40)
);

insert into PUBLIC.sample_table
values(1, 'NAME 1');

insert into PUBLIC.sample_table
values(2, 'NAME 2');

insert into PUBLIC.sample_table
values(3, 'NAME 3');

insert into PUBLIC.sample_table
values(4, 'NAME 4');

insert into PUBLIC.sample_table
values(5, 'NAME 5');

insert into PUBLIC.sample_table
values(6, 'NAME 6');

Oracle

Query

CREATE OR REPLACE PROCEDURE sp_sample1 AS
-- Rowtype variable
rowtype_variable sample_table%rowtype;

--Record variable
TYPE record_typ_def IS RECORD(ID number, NAME varchar2(23));
record_variable_def record_typ_def;

-- Auxiliary variable
name_var VARCHAR(20);
BEGIN
   SELECT * INTO rowtype_variable FROM sample_table WHERE ID = 1 FETCH NEXT 1 ROWS ONLY;
   name_var := rowtype_variable.NAME;
   INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 1', name_var);
  
   SELECT ID, NAME INTO rowtype_variable FROM sample_table WHERE ID = 2 FETCH NEXT 1 ROWS 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 = 3 FETCH NEXT 1 ROWS ONLY;
   name_var := record_variable_def.NAME;
   INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 3', name_var);
  
   SELECT ID, NAME INTO record_variable_def FROM sample_table WHERE ID = 4 FETCH NEXT 1 ROWS ONLY;
   name_var := record_variable_def.NAME;
   INSERT INTO RESULTS(COL1, COL2) VALUES('SELECT 4', name_var);
END;


call sp_sample1();

SELECT * FROM results;

Result

|COL1    |COL2  |
|--------|------|
|SELECT 1|NAME 1|
|SELECT 2|NAME 2|
|SELECT 3|NAME 3|
|SELECT 4|NAME 4|

Snowflake

Query

CREATE OR REPLACE PROCEDURE PUBLIC.sp_sample1 ()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
   DECLARE
      -- Rowtype variable
      rowtype_variable OBJECT /*** MSC-WARNING - MSCEWI1036 - ROWTYPE DATA TYPE CONVERTED TO OBJECT ***/ := OBJECT_CONSTRUCT();

-- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO OBJECT **
--      TYPE record_typ_def IS RECORD(ID NUMBER(38, 18), NAME VARCHAR(23) /*** MSC-WARNING - MSCEWI1036 - VARCHAR2 DATA TYPE CONVERTED TO VARCHAR ***/)
                                                                                                                                                     ;

      --Record variable
      record_variable_def OBJECT := OBJECT_CONSTRUCT() /*** MSC-WARNING - MSCEWI1036 - record_typ_def DATA TYPE CONVERTED TO OBJECT ***/;

      -- Auxiliary variable
      name_var VARCHAR(20);
   BEGIN
      SELECT
         OBJECT_CONSTRUCT( *) INTO rowtype_variable FROM
         PUBLIC.sample_table
      WHERE ID = 1
      FETCH NEXT 1 ROWS ONLY;
      name_var := :rowtype_variable:NAME;
      INSERT INTO PUBLIC.RESULTS(COL1, COL2) VALUES('SELECT 1', :name_var);

      SELECT
         OBJECT_CONSTRUCT('ID', ID, 'NAME', NAME) INTO rowtype_variable FROM
         PUBLIC.sample_table
      WHERE ID = 2
      FETCH NEXT 1 ROWS ONLY;
      name_var := :rowtype_variable:NAME;
      INSERT INTO PUBLIC.RESULTS(COL1, COL2) VALUES('SELECT 2', :name_var);

      SELECT
         OBJECT_CONSTRUCT( *) INTO
         :record_variable_def
      FROM
         PUBLIC.sample_table
      WHERE ID = 3
      FETCH NEXT 1 ROWS ONLY;
      name_var := :record_variable_def:NAME;
      INSERT INTO PUBLIC.RESULTS(COL1, COL2) VALUES('SELECT 3', :name_var);

      SELECT
         OBJECT_CONSTRUCT('ID', ID, 'NAME', NAME) INTO
         :record_variable_def
      FROM
         PUBLIC.sample_table
      WHERE ID = 4
      FETCH NEXT 1 ROWS ONLY;
      name_var := :record_variable_def:NAME;
      INSERT INTO PUBLIC.RESULTS(COL1, COL2) VALUES('SELECT 4', :name_var);
   END;
$$;

CALL PUBLIC.sp_sample1();

SELECT * FROM
   PUBLIC.results;

Result

|COL1    |COL2  |
|--------|------|
|SELECT 1|NAME 1|
|SELECT 2|NAME 2|
|SELECT 3|NAME 3|
|SELECT 4|NAME 4|

Known Issues

1. The following functionalities are currently not being converted:

• Fetching data into a Record.

• Nested records (Records inside records).

• Collections inside records.

Related EWIs

1. MSCEWI1073: Pending Functional Equivalence Review.

2. MSCEWI1056: Custom Types Not Supported.

3. MSCEWI1058: Functionality is not currently supported by Snowflake Scripting.

4. ​MSCEWI1062​: Custom type usage changed to variant.

5. MSCEWI1036: