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.

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.

(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

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

Query

IN -> Oracle_01.sql

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

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE record_table (col1 FLOAT,
    col2 INTEGER)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE record_procedure ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    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

    BEGIN
        INSERT 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;--usage

    END;
$$;

CALL record_procedure();

SELECT * FROM
    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

IN -> Oracle_02.sql

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;