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

Query

IN -> Oracle_01.sql

CREATE TABLE table1(
col1 NUMBER
);

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

Snowflake

Query

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

Query

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

Query

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

Query

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

Query

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.

Oracle

Query

IN -> Oracle_04.sql

CREATE OR REPLACE PROCEDURE procedure1
IS
TYPE collection_type IS TABLE OF NUMBER;
collection_var collection_type;
var1 collection_var%TYPE;
BEGIN
NULL;
END;

Snowflake

Query

OUT -> Oracle_04.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-0058 - FUNCTIONALITY FOR 'PL COLLECTION TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING ***/!!!
TYPE collection_type IS TABLE OF NUMBER;
collection_var VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'collection_type' USAGE CHANGED TO VARIANT ***/!!!;
var1 VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-OR0129 - TYPE ATTRIBUTE 'collection_var%TYPE' COULD NOT BE RESOLVED, SO IT WAS TRANSFORMED TO VARIANT ***/!!!;
BEGIN
NULL;
END;
$$;

TYPE attribute for cursors

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

Oracle

Query

IN -> Oracle_05.sql

CREATE TABLE table1 (col1 NUMBER);

CREATE OR REPLACE PROCEDURE procedure1
IS
TYPE cursor_type IS REF CURSOR RETURN table1%ROWTYPE;
cursor_var cursor_type;
var1 cursor_var%TYPE;
BEGIN
NULL;
END;

Snowflake

Query

OUT -> Oracle_05.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
----** SSC-FDM-0024 - FUNCTIONALITY FOR 'PL REF CURSOR TYPE DEFINITION' IS NOT CURRENTLY SUPPORTED BY SNOWFLAKE SCRIPTING **
--TYPE cursor_type IS REF CURSOR RETURN table1%ROWTYPE;
cursor_var_res RESULTSET;
var1_res RESULTSET;
BEGIN
NULL;
END;
$$;

For those cases when the data type of the referenced item cannot be obtained, the referencing item TYPE attribute is changed to VARIANT.

Knows Issues

1. Cursors and collections declarations are not supported.

Collection and cursor variable declarations are not supported yet so the referencing item TYPE attribute is changed to VARIANT and a warning is added in these cases.

2. Original data type could not be obtained.

When the referenced item data type could not be obtained the referencing item TYPE attribute is changed to VARIANT and a warning is added.

Related EWIS

1. SSC-FDM-0024: Functionality is not currently supported by Snowflake Scripting.
2. SSC-EWI-OR0129: The statement below has usages of nested cursors.
3. SSC-FDM-0006: Number type column may not behave similarly in Snowflake.
4. SSC-EWI-0062: Custom type usage changed to variant.