TYPE attribute

Description

This chapter is related to transforming the TYPE attribute when it references a column, variable, record, collection, or cursor. The transformation involves getting the referenced item data type and replacing the referencing item TYPE attribute for the data type obtained.

Sample Source Patterns

TYPE attribute for columns

In this case, the referenced item is a column from a table created previously.

Oracle

IN -> Oracle_01.sql

CREATE TABLE table1(
col1 NUMBER
);

CREATE OR REPLACE PROCEDURE procedure1
IS
var1 table1.col1%TYPE;
BEGIN
NULL;
END;

Snowflake

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE table1 (
col1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

CREATE OR REPLACE PROCEDURE procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
var1 NUMBER(38, 18);
BEGIN
NULL;
END;
$$;

Further information about NUMBER datatype can be found in Numeric Data Types section.

TYPE attribute for variables

In this case, the referenced item is a variable declared previously.

Oracle

IN -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE procedure1
IS
var0 FLOAT;
var1 var0%TYPE;
var2 var1%TYPE;
var3 var2%TYPE;
BEGIN
NULL;
END;

Snowflake

OUT -> Oracle_02.sql

CREATE OR REPLACE PROCEDURE procedure1 ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
var0 FLOAT;
var1 FLOAT;
var2 FLOAT;
var3 FLOAT;
BEGIN
NULL;
END;
$$;

Further information about FLOAT datatype can be found in FLOAT Data Type section

TYPE attribute for records

In this case, the referenced item is a record declared previously.

Oracle

IN -> Oracle_03.sql

CREATE OR REPLACE PROCEDURE procedure1
IS
TYPE record_typ_def IS RECORD(field1 NUMBER);
record_var record_typ_def;
var1 record_var%TYPE;
var2 record_var.field1%TYPE;
BEGIN
NULL;
END;

Snowflake

OUT -> Oracle_03.sql

CREATE OR REPLACE PROCEDURE procedure1 ()
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_def IS RECORD(field1 NUMBER);
record_var OBJECT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - record_typ_def DATA TYPE CONVERTED TO OBJECT ***/!!! := OBJECT_CONSTRUCT();
var1 OBJECT := OBJECT_CONSTRUCT();
var2 NUMBER(38, 18);
BEGIN
NULL;
END;
$$;

In the example before, the variable which is referencing the record variable is changed to OBJECT as same as the record variable, and the variable which is referencing the record field is changed to the record field data type (NUMBER (38, 18)).

These changes don't work for embedded records.

Further information about records can be found in Collection & Records section.

TYPE attribute for collections

In this case, the referenced item is a collection variable, but since collections are not supported, the referencing item TYPE attribute is changed to VARIANT data type.