SnowConvert: Oracle Data Types Translation Reference¶
Data Type Customization¶
Data Type Conversion during a database migration process is something that many users need to personalize and set up precision values more accurately. For this reason, SnowConvert enables the Data Type Customization to specify rules of data type transformation considering data type origin and column name.
Supported Data Types¶
The following data types are supported
NUMBER
Structure of JSON¶
SnowConvert receives a JSON file with the following structure.
{
"types" : {
},
"columns": [
{
"nameExpression" : "",
"targetType" : ""
}
]
}
The object types defines the origin data type and the data type the target should be. Also, you might define a general rule that converts the origin data type with or without precision.
For Example:
"types" : {
"NUMBER" : "NUMBER(11, 2)",
"NUMBER(10, 0)" : "NUMBER(3, 0)"
}
The object columns is an array, and each object of the array defines the name or the regex expression of the columns to change the type.
"columns": [
{
"nameExpression" : "MONTH",
"targetType" : "NUMBER(2,0)"
}
]
When using regular expressions customization, the migration could be affected if the regex isn’t correct.
Customization Priority¶
There might be rules of customization that apply to the same object; however, only one will be chosen. SnowConvert will consider the following considerations to prioritize which rule to follow.
The first rule in columns is located from top to bottom.
The rule defined in type considering the precision.
The general rule is defined for Data Type.
The rule is defined in SnowConvert.
Example¶
Let’s say we have the following input code.
Input Code
CREATE TABLE employees (
employee_ID NUMBER,
manager_YEAR NUMBER(10, 0),
manager_MONTH NUMBER(10, 0)
);
And the following renaming information
Customization File (.JSON)
{
"types" : {
"NUMBER" : "NUMBER(11, 2)",
"NUMBER(10, 0)" : "NUMBER(3, 0)"
},
"columns": [
{
"nameExpression" : "MONTH",
"targetType" : "NUMBER(2,0)"
}
]
}
This would be the output code with and without customization data types.
Snowflake output code¶
Without renaming:
CREATE OR REPLACE TABLE employees (
employee_ID NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
manager_YEAR NUMBER(10) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
manager_MONTH NUMBER(10) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
;
With renaming:
CREATE OR REPLACE TABLE employees (
employee_ID NUMBER(11, 2),
manager_YEAR NUMBER(3, 0),
manager_MONTH NUMBER(2, 0)
)
;
Notice how all NUMBER types are migrated to the types specified in json file.
ANYDATA¶
Description¶
This type contains an instance of a given type, with data, plus a description of the type. ANYDATA can be used as a table column data type and lets you store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types. (Oracle SQL Language Reference ANYDATA Data Type).
The ANYDATA data type is not supported in Snowflake.
{ SYS.ANYDATA | ANYDATA }
Sample Source Patterns¶
Create Table with ANYDATA¶
Oracle¶
CREATE TABLE anydatatable
(
col1 NUMBER,
col2 ANYDATA,
col3 SYS.ANYDATA
);
Snowflake¶
CREATE OR REPLACE TABLE anydatatable
(
col1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col2 VARIANT,
col3 VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Inserting data into ANYDATA column¶
Oracle¶
INSERT INTO anydatatable VALUES(
555,
ANYDATA.ConvertVarchar('Another Test Text')
);
Snowflake¶
INSERT INTO anydatatable
VALUES(
555,
ANYDATA.ConvertVarchar('Another Test Text') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertVarchar' NODE ***/!!!
);
Functional Example¶
This example is not a translation of SnowConvert, it is only used to show the functional equivalence between Oracle ANYDATA and Snowflake VARIANT
We are using the ANYDATA built-in package. The conversion for this package is currently not supported by SnowConvert.
Oracle¶
--Create Table
CREATE TABLE anydatatable_example
(
col1 ANYDATA,
col2 ANYDATA,
col3 ANYDATA,
col4 ANYDATA,
col5 ANYDATA
);
--Insert data
INSERT INTO anydatatable_example VALUES(
ANYDATA.ConvertNumber(123),
ANYDATA.ConvertVarchar('Test Text'),
ANYDATA.ConvertBFloat(3.14f),
ANYDATA.ConvertDate(CURRENT_DATE),
ANYDATA.ConvertTimestamp(CURRENT_TIMESTAMP)
);
--Retrieve information
SELECT
ANYDATA.AccessNumber(col1) AS col1,
ANYDATA.AccessVarchar(col2) AS col2,
ANYDATA.AccessBFloat(col3) AS col3,
ANYDATA.AccessDate(col4) AS col4,
ANYDATA.AccessTimestamp(col5) AS col5
FROM anydatatable_example;
Result¶
COL1|COL2 |COL3|COL4 |COL5 |
----+---------+----+-----------------------+-----------------------+
123|Test Text|3.14|2021-12-05 18:24:59.000|2021-12-05 18:24:59.100|
Snowflake¶
--Create Table
CREATE OR REPLACE TABLE anydatatable_example
(
col1 VARIANT,
col2 VARIANT,
col3 VARIANT,
col4 VARIANT,
col5 VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
--Insert data
INSERT INTO anydatatable_example
VALUES(
ANYDATA.ConvertNumber(123) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertNumber' NODE ***/!!!,
ANYDATA.ConvertVarchar('Test Text') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertVarchar' NODE ***/!!!,
ANYDATA.ConvertBFloat(3.14) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertBFloat' NODE ***/!!!,
ANYDATA.ConvertDate(CURRENT_DATE()) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertDate' NODE ***/!!!,
ANYDATA.ConvertTimestamp(CURRENT_TIMESTAMP()) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertTimestamp' NODE ***/!!!
);
--Retrieve information
SELECT
ANYDATA.AccessNumber(col1) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.AccessNumber' NODE ***/!!! AS col1,
ANYDATA.AccessVarchar(col2) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.AccessVarchar' NODE ***/!!! AS col2,
ANYDATA.AccessBFloat(col3) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.AccessBFloat' NODE ***/!!! AS col3,
ANYDATA.AccessDate(col4) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.AccessDate' NODE ***/!!! AS col4,
ANYDATA.AccessTimestamp(col5) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.AccessTimestamp' NODE ***/!!! AS col5
FROM
anydatatable_example;
Result¶
COL1|COL2 |COL3|COL4 |COL5 |
----+-----------+----+------------+-------------------------------+
123 |"Test Text"|3.14|"2021-12-05"|"2021-12-05 18:24:43.326 -0800"|
Known Issues¶
1. No access to the ANYDATA built-in package¶
Most operations with ANYDATA columns require to use the ANYDATA built-in package, transformation for Oracle built-in packages is not supported by SnowConvert yet.
ANYDATASET¶
Description¶
This type contains a description of a given type plus a set of data instances of that type. ANYDATASET can be used as a procedure parameter data type where such flexibility is needed. The values of the data instances can be of SQL built-in types as well as user-defined types. (Oracle SQL Language Reference ANYDATASET Data Type).
The ANYDATASET data type is not supported in Snowflake. A possible workaround for this data type could be Snowflake ARRAY, however that transformation is currently not supported by SnowConvert.
{ SYS.ANYDATASET | ANYDATASET }
Sample Source Patterns¶
Create Table with ANYDATASET¶
Oracle¶
CREATE TABLE anydatasettable
(
col1 NUMBER,
col2 ANYDATASET,
col3 SYS.ANYDATASET
);
Snowflake¶
CREATE OR REPLACE TABLE anydatasettable
(
col1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
col2 ANYDATASET,
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
col3 SYS.ANYDATASET
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Inserting data into ANYDATASET column¶
Oracle¶
DECLARE
anytype_example ANYTYPE;
anydataset_example ANYDATASET;
BEGIN
ANYDATASET.BEGINCREATE(DBMS_TYPES.TYPECODE_VARCHAR2, anytype_example, anydataset_example);
anydataset_example.ADDINSTANCE;
anydataset_example.SETVARCHAR2('First element');
anydataset_example.ADDINSTANCE;
anydataset_example.SETVARCHAR2('Second element');
ANYDATASET.ENDCREATE(anydataset_example);
INSERT INTO anydatasettable VALUES (123, anydataset_example);
END;
Snowflake¶
DECLARE
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
anytype_example ANYTYPE;
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
anydataset_example ANYDATASET;
BEGIN
CALL
ANYDATASET.BEGINCREATE(
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_TYPES.TYPECODE_VARCHAR2' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS TYPECODE_VARCHAR2, :anytype_example, :anydataset_example);
CALL
anydataset_example.ADDINSTANCE();
CALL
anydataset_example.SETVARCHAR2('First element');
CALL
anydataset_example.ADDINSTANCE();
CALL
anydataset_example.SETVARCHAR2('Second element');
CALL
ANYDATASET.ENDCREATE(:anydataset_example);
INSERT INTO anydatasettable
VALUES (123, :anydataset_example);
END;
Known Issues¶
1. Inserts are being parsed incorrectly¶
Some of the functions needed to create and insert a new ANYDATASET object are not being parsed correctly by SnowConvert.
2. No access to the ANYDATASET built-in package¶
Most operations with ANYDATASET columns require to use the ANYDATASET built-in package, transformation for Oracle built-in packages is not supported by SnowConvert yet.
Related EWIs¶
SSC-EWI-OR0076: Built In Package Not Supported.
SSC-FDM-0006: Number type column may not behave similarly in Snowflake
SSC-EWI-0028: Type not supported by Snowflake.
ANYTYPE¶
Description¶
This type can contain a type description of any named SQL type or unnamed transient type. (Oracle SQL Language Reference ANYTYPE Data Type).
The ANYTYPE data type is not supported in Snowflake.
{ SYS.ANYTYPE | ANYTYPE }
Sample Source Patterns¶
Create Table with ANYTYPE¶
Oracle¶
CREATE TABLE anytypetable
(
col1 NUMBER,
col2 ANYTYPE,
col3 SYS.ANYTYPE
);
Snowflake¶
CREATE OR REPLACE TABLE anytypetable
(
col1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
col2 ANYTYPE,
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
col3 SYS.ANYTYPE
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Inserting data into ANYTYPE column¶
Oracle¶
--Create Custom Type
CREATE OR REPLACE TYPE example_type AS OBJECT (id NUMBER, name VARCHAR(20));
--Insert
INSERT INTO anytypetable VALUES(
123,
GETANYTYPEFROMPERSISTENT ('HR', 'EXAMPLE_TYPE')
);
Snowflake¶
--Create Custom Type
CREATE OR REPLACE TYPE example_type AS OBJECT (id NUMBER, name VARCHAR(20))
;
--Insert
INSERT INTO anytypetable
VALUES(
123,
GETANYTYPEFROMPERSISTENT ('HR', 'EXAMPLE_TYPE') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'GETANYTYPEFROMPERSISTENT' NODE ***/!!!
);
Known Issues¶
1. No access to the ANYTYPE built-in package¶
Most operations with ANYDATA columns require to use the ANYTYPE built-in package, transformation for Oracle built-in packages is not supported by SnowConvert yet.
Related EWIs¶
SSC-EWI-0056: Create Type Not Supported.
SSC-EWI-0073: Pending Functional Equivalence Review.
SSC-EWI-0028: Type not supported in Snowflake.
SSC-FDM-0006: Number type column may not behave similarly in Snowflake.
ROWID¶
Description¶
The rows in heap-organized tables that are native to Oracle Database have row addresses called rowids. You can examine a rowid row address by querying the pseudocolumn ROWID. Values of this pseudocolumn are strings representing the address of each row. These strings have the data type ROWID. You can also create tables and clusters that contain actual columns having the ROWID data type. (Oracle SQL Language Reference ROWID Data Types)
Sample Source Patterns¶
ROWID in Create Table¶
Oracle¶
CREATE TABLE rowid_table
(
rowid_column ROWID
);
Snowflake¶
CREATE OR REPLACE TABLE rowid_table
(
rowid_column VARCHAR(18) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - ROWID DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Insert data in the ROWID column¶
It is possible to insert data in ROWID columns if the insert has a valid ROWID, as shown in the example below. Unfortunately retrieving ROWID from a table is not allowed.
Oracle¶
INSERT INTO rowid_table VALUES ('AAATtCAAMAAAADLABD');
SELECT rowid_column FROM rowid_table;
|ROWID_COLUMN |
|------------------|
|AAATtCAAMAAAADLABD|
Snowflake¶
INSERT INTO rowid_table
VALUES ('AAATtCAAMAAAADLABD');
SELECT rowid_column FROM
rowid_table;
|ROWID_COLUMN |
|------------------|
|AAATtCAAMAAAADLABD|
Known Issues¶
Since the result set is too large, Row Limiting Clause was added. You can remove this clause to retrieve the entire result set.
1. Retrieving ROWID from a table that does not have an explicit column with this data type¶
As mentioned in the Snowflake forum, ROWID is not supported by Snowflake. The following query displays an error in Snowflake since hr.employees do not contain a ROWID column.
Oracle¶
SELECT
ROWID
FROM
hr.employees
FETCH NEXT 10 ROWS ONLY;
|ROWID |
|------------------|
|AAATtCAAMAAAADLABD|
|AAATtCAAMAAAADLABV|
|AAATtCAAMAAAADLABX|
|AAATtCAAMAAAADLAAv|
|AAATtCAAMAAAADLAAV|
|AAATtCAAMAAAADLAAD|
|AAATtCAAMAAAADLABL|
|AAATtCAAMAAAADLAAP|
|AAATtCAAMAAAADLAA6|
|AAATtCAAMAAAADLABg|
Snowflake¶
SELECT
--** SSC-FDM-OR0030 - ROWID PSEUDOCOLUMN IS NOT SUPPORTED IN SNOWFLAKE, IT WAS CONVERTED TO NULL TO AVOID RUNTIME ERRORS **
'' AS ROWID
FROM
hr.employees
FETCH NEXT 10 ROWS ONLY;
SQL compilation error: invalid identifier ‘ROWID’
Related EWIs¶
SSC-EWI-0036: Data type converted to another data type.
SSC-FDM-OR0030: ROWID pseudocolumn is not supported in Snowflake.
UROWID¶
Description¶
Oracle uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids.(Oracle SQL Language Reference UROWID Data Type)
UROWID [(size)]
Sample Source Patterns¶
UROWID in Create Table¶
Oracle¶
CREATE TABLE urowid_table
(
urowid_column UROWID,
urowid_sized_column UROWID(40)
);
Snowflake¶
CREATE OR REPLACE TABLE urowid_table
(
urowid_column VARCHAR(18) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - UROWID DATA TYPE CONVERTED TO VARCHAR ***/!!!,
urowid_sized_column VARCHAR(18) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - UROWID DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Insert data in the UROWID column¶
Just like ROWID, it is possible to insert data in UROWID columns if the insert has a valid UROWID, but retrieving from a table is not allowed.
Oracle¶
INSERT INTO urowid_table VALUES ('*BAMAAJMCVUv+','*BAMAAJMCVUv+');
SELECT * FROM urowid_table;
|UROWID_COLUMN|UROWID_SIZED_COLUMN|
|-------------|-------------------|
|*BAMAAJMCVUv+|*BAMAAJMCVUv+ |
Snowflake** SSC-FDM-0007 - MISSING DEPENDENT OBJECT “urowid_table” **¶
INSERT INTO urowid_table
VALUES ('*BAMAAJMCVUv+','*BAMAAJMCVUv+');
SELECT * FROM
urowid_table;
|UROWID_COLUMN|UROWID_SIZED_COLUMN|
|-------------|-------------------|
|*BAMAAJMCVUv+|*BAMAAJMCVUv+ |
Known Issues¶
Since the result set is too large, Row Limiting Clause was added. You can remove this clause to retrieve the entire result set.
1. Retrieving UROWID from a table that does not have an explicit column with this data type
The following query displays an error in Snowflake since hr.countries do not contain a ROWID (as mentioned in Oracle’s documentation UROWID is accessed with SELECT … ROWID statement) column.
Oracle¶
SELECT
rowid,
country_name
FROM
hr.countries FETCH NEXT 10 ROWS ONLY;
|ROWID |COUNTRY_NAME|
|-------------|------------|
|*BAMAAJMCQVL+|Argentina |
|*BAMAAJMCQVX+|Australia |
|*BAMAAJMCQkX+|Belgium |
|*BAMAAJMCQlL+|Brazil |
|*BAMAAJMCQ0H+|Canada |
|*BAMAAJMCQ0j+|Switzerland |
|*BAMAAJMCQ07+|China |
|*BAMAAJMCREX+|Germany |
|*BAMAAJMCREv+|Denmark |
|*BAMAAJMCRUf+|Egypt |
Snowflake¶
SELECT
--** SSC-FDM-OR0030 - ROWID PSEUDOCOLUMN IS NOT SUPPORTED IN SNOWFLAKE, IT WAS CONVERTED TO NULL TO AVOID RUNTIME ERRORS **
'' AS rowid,
country_name
FROM
hr.countries
FETCH NEXT 10 ROWS ONLY;
SQL compilation error: invalid identifier ‘ROWID’
2. EWI should be displayed by SnowConvert¶
EWI should be displayed when trying to select UROWID column. There is a work item to add the corresponding EWI. This issue has been marked as critical and will be fixed in the upcoming releases.
Related EWIs¶
SSC-EWI-0036: Data type converted to another data type.
SSC-FDM-OR0030: ROWID pseudocolumn is not supported in Snowflake.
SDO_TOPO_GEOMETRY¶
Some parts in the output code are omitted for clarity reasons.
Description¶
This type describes a topology geometry, which is stored in a single row, in a single column of object type SDO_TOPO_GEOMETRY in a user-defined table. (Oracle SQL Language Reference SDO_TOPO_GEOMETRY Data Type).
Definition of SDO_TOPO_GEOMETRY object:
CREATE TYPE SDO_TOPO_GEOMETRY AS OBJECT
(tg_type NUMBER,
tg_id NUMBER,
tg_layer_id NUMBER,
topology_id NUMBER);
/
The SDO_TOPO_GEOMETRY object is not supported in Snowflake.
Sample Source Patterns¶
SDO_TOPO_GEOMETRY in Create Table¶
Oracle¶
CREATE TABLE topo_geometry_table(
topo_geometry_column SDO_TOPO_GEOMETRY
);
Snowflake¶
CREATE OR REPLACE TABLE topo_geometry_table (
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
topo_geometry_column SDO_TOPO_GEOMETRY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Inserting data in SDO_TOPO_GEOMETRY Table¶
Oracle¶
INSERT INTO topo_geometry_table VALUES (SDO_TOPO_GEOMETRY(1,2,3,4));
INSERT INTO topo_geometry_table VALUES (NULL);
Snowflake¶
INSERT INTO topo_geometry_table
VALUES (SDO_TOPO_GEOMETRY(1,2,3,4) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_TOPO_GEOMETRY' NODE ***/!!!);
INSERT INTO topo_geometry_table
VALUES (NULL);
Known Issues¶
1. SDO_TOPO_GEOMETRY Data Type not transformed
SDO_TOPO_GEOMETRY Data Type is not being transformed by SnowConvert.
Related EWIs¶
SSC-EWI-0028: Type not supported.
SSC-EWI-0073: Pending functional equivalence review.
SDO_GEOMETRY¶
Some parts in the output code are omitted for clarity reasons.
Description¶
The geometric description of a spatial object is stored in a single row, in a single column of object type SDO_GEOMETRY in a user-defined table. Any table that has a column of type SDO_GEOMETRY must have another column, or set of columns, that defines a unique primary key for that table. (Oracle SQL Language Reference SDO_GEOMETRY Data Type)
Definition of SDO_GEOMETRY object:
CREATE TYPE SDO_GEOMETRY AS OBJECT
(sgo_gtype NUMBER,
sdo_srid NUMBER,
sdo_point SDO_POINT_TYPE,
sdo_elem_info SDO_ELEM_INFO_ARRAY,
sdo_ordinates SDO_ORDINATE_ARRAY);
/
The SDO_GEOMETRY object is not supported in Snowflake. A workaround for this data type is to use Snowflake GEOGRAPHY, however that transformation is currently not supported by SnowConvert.
Sample Source Patterns¶
SDO_GEOMETRY in Create Table¶
Oracle¶
CREATE TABLE geometry_table(
geometry_column SDO_GEOMETRY
);
Snowflake¶
CREATE OR REPLACE TABLE geometry_table (
geometry_column GEOMETRY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Inserting data in SDO_GEOMETRY Table¶
Oracle¶
INSERT INTO geometry_table VALUES (
SDO_GEOMETRY('POINT(-79 37)')
);
INSERT INTO geometry_table VALUES (
SDO_GEOMETRY('LINESTRING(1 3, 1 5, 2 7)')
);
INSERT INTO geometry_table VALUES (
MDSYS.SDO_GEOMETRY(
2001,
8307,
MDSYS.SDO_POINT_TYPE (
-86.13631,
40.485424,
NULL),
NULL,
NULL
)
);
INSERT INTO geometry_table VALUES (
SDO_GEOMETRY(
2003,
12,
SDO_POINT_TYPE(12, 14, -5),
SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(1,1, 5,7)
)
);
INSERT INTO geometry_table VALUES (
NULL);
Snowflake¶
INSERT INTO geometry_table
VALUES (
SDO_GEOMETRY('POINT(-79 37)') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_GEOMETRY' NODE ***/!!!
);
INSERT INTO geometry_table
VALUES (
SDO_GEOMETRY('LINESTRING(1 3, 1 5, 2 7)') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_GEOMETRY' NODE ***/!!!
);
INSERT INTO geometry_table
VALUES (
MDSYS.SDO_GEOMETRY(
2001,
8307,
MDSYS.SDO_POINT_TYPE (
-86.13631,
40.485424,
NULL) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'MDSYS.SDO_POINT_TYPE' NODE ***/!!!,
NULL,
NULL
) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'MDSYS.SDO_GEOMETRY' NODE ***/!!!
);
INSERT INTO geometry_table
VALUES (
SDO_GEOMETRY(
2003,
12,
SDO_POINT_TYPE(12, 14, -5) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_POINT_TYPE' NODE ***/!!!,
SDO_ELEM_INFO_ARRAY(1,1003,3) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_ELEM_INFO_ARRAY' NODE ***/!!!,
SDO_ORDINATE_ARRAY(1,1, 5,7) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_ORDINATE_ARRAY' NODE ***/!!!
) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_GEOMETRY' NODE ***/!!!
);
INSERT INTO geometry_table
VALUES (
NULL);
Migration using the GEOGRAPHY data type¶
Oracle¶
CREATE TABLE geometry_table(
geometry_column SDO_GEOMETRY
);
INSERT INTO geometry_table VALUES (
SDO_GEOMETRY('POINT(-79 37)')
);
INSERT INTO geometry_table VALUES (
SDO_GEOMETRY('LINESTRING(1 3, 1 5, 2 7)')
);
/*
--NOT SUPPORTED BY SNOWFLAKE GEOGRAPHY
INSERT INTO geometry_table VALUES (
MDSYS.SDO_GEOMETRY(
2001,
8307,
MDSYS.SDO_POINT_TYPE (
-86.13631,
40.485424,
NULL),
NULL,
NULL
)
);
INSERT INTO geometry_table VALUES (
SDO_GEOMETRY(
2003,
12,
SDO_POINT_TYPE(12, 14, -5),
SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(1,1, 5,7)
)
);
*/
SELECT * FROM geometry_table;
GEOMETRY_COLUMN |
--------------------------------------------------------+
[2001, null, [-79, 37, null], [NULL], [NULL]] |--(POINT(-79,37))
[2002, null, [null, null, null], [1,2,1], [1,3,1,5,2,7]]|--(LINESTRING(1 3, 1 5, 2 7))
Snowflake¶
CREATE OR REPLACE TABLE geometry_table (
geometry_column GEOMETRY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO geometry_table
VALUES (
SDO_GEOMETRY('POINT(-79 37)') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_GEOMETRY' NODE ***/!!!
);
INSERT INTO geometry_table
VALUES (
SDO_GEOMETRY('LINESTRING(1 3, 1 5, 2 7)') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'SDO_GEOMETRY' NODE ***/!!!
);
/*
--NOT SUPPORTED BY SNOWFLAKE GEOGRAPHY
INSERT INTO geometry_table VALUES (
MDSYS.SDO_GEOMETRY(
2001,
8307,
MDSYS.SDO_POINT_TYPE (
-86.13631,
40.485424,
NULL),
NULL,
NULL
)
);
INSERT INTO geometry_table VALUES (
SDO_GEOMETRY(
2003,
12,
SDO_POINT_TYPE(12, 14, -5),
SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(1,1, 5,7)
)
);
*/
SELECT * FROM
geometry_table;
GEOMETRY_COLUMN |
-----------------------+
POINT(-79 37) |
LINESTRING(1 3,1 5,2 7)|
Related EWIs¶
SSC-EWI-0073: Pending Functional Equivalence Review.
SDO_GEORASTER¶
Some parts in the output code are omitted for clarity reasons.
Description¶
In the GeoRaster object-relational model, a raster grid or image object is stored in a single row, in a single column of object type SDO_GEORASTER in a user-defined table. (Oracle SQL Language Reference SDO_GEORASTER Data Type).
Definition of SDO_GEORASTER object:
CREATE TYPE SDO_GEORASTER AS OBJECT
(rasterType NUMBER,
spatialExtent SDO_GEOMETRY,
rasterDataTable VARCHAR2(32),
rasterID NUMBER,
metadata XMLType);
/
SDO_GEORASTER is disabled by default, to enable its usage, follow the steps described in this section of Oracle documentation.
The SDO_GEORASTER object is not supported in Snowflake.
Sample Source Patterns¶
SDO_GEORASTER in Create Table¶
Oracle¶
CREATE TABLE georaster_table(
georaster_column SDO_GEORASTER
);
Snowflake¶
CREATE OR REPLACE TABLE georaster_table (
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
georaster_column SDO_GEORASTER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
Inserting data in SDO_GEORASTER Table¶
Oracle¶
INSERT INTO georaster_table VALUES (null);
INSERT INTO georaster_table VALUES (sdo_geor.init('RDT_11', 1));
Snowflake¶
INSERT INTO georaster_table
VALUES (null);
INSERT INTO georaster_table
VALUES (
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'sdo_geor.init' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS init);
Known Issues¶
1. SDO_GEORASTER Data Type not transformed
SDO_GEORASTER Data Type is not being transformed by SnowConvert.
Related EWIs¶
SSC-EWI-0028: Type not supported.
SSC-EWI-OR0076: Built In Package Not Supported.
RAW and LONG RAW¶
Description¶
The RAW and LONG RAW data types store data that is not to be explicitly converted by Oracle Database when moving data between different systems. These data types are intended for binary data or byte strings. (Oracle SQL Language Reference Row and Long Raw Data Types)
{ LONG RAW | RAW (size) }
Sample Source Patterns¶
Raw and Long Raw in Create Table¶
Oracle¶
CREATE TABLE raw_table
(
id INTEGER,
raw_column RAW(2000),
long_raw_column LONG RAW
);
// pragma: allowlist nextline secret
INSERT INTO raw_table values(1, 'FF00FF00FF', 'FF00FF00FFAABAABABABABA917843210984237123ABABABABAABBAAABBACDFFD');
// pragma: allowlist nextline secret
INSERT INTO raw_table values(2, 'AAAAAAAAAA', 'ABABABABABABABABABABABABABABABAbABAbABAABABAAABABABABABABABABABABA')
--Insert with largest string posible (2000 HEX characters)
INSERT INTO raw_table VALUES (3, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA', 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
Snowflake CREATE OR REPLACE TABLE raw_table¶
CREATE OR REPLACE TABLE raw_table
(
id INTEGER,
raw_column BINARY,
long_raw_column BINARY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
// pragma: allowlist nextline secret
INSERT INTO raw_table values(1, 'FF00FF00FF', 'FF00FF00FFAABAABABABABA917843210984237123ABABABABAABBAAABBACDFFD');
// pragma: allowlist nextline secret
INSERT INTO raw_table values(2, 'AAAAAAAAAA', 'ABABABABABABABABABABABABABABABAbABAbABAABABAAABABABABABABABABABABA');
--Insert with largest string posible (2000 HEX characters)
INSERT INTO raw_table
VALUES (3, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA', 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
Retrieving data from Raw and Long Raw column¶
Oracle¶
SELECT * FROM raw_table ORDER BY id;
ID|RAW_COLUMN |LONG_RAW_COLUMN |
--+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1| | ªº««««© 2 B7 :ºººº«ºª»¬ßý |
2|ªªªªª |«««««««««««««««««««ªººªºººººººººº |
3|ªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªª|ªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªª|
Snowflake¶
SELECT * FROM
raw_table
ORDER BY id;
ID|RAW_COLUMN |LONG_RAW_COLUMN |
--+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1| | ªº««««© 2 B7 :ºººº«ºª»¬ßý |
2|ªªªªª |«««««««««««««««««««ªººªºººººººººº |
3|ªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªª|ªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªªª|
Known Issues¶
No issues were found.
Related EWIs ¶
No related EWIs.
JSON Data Type¶
Description¶
Oracle Database supports JSON natively with relational database features, including transactions, indexing, declarative querying, and views. Unlike relational data, JSON data can be stored in the database, indexed, and queried without any need for a schema that defines the data. (Oracle SQL Language Reference JSON Data Type).
The JSON data types are transformed to VARIANT in order to emulate the Oracle behavior.
Sample Source Patterns¶
JSON Data Type as a column in Create Table¶
Oracle¶
CREATE TABLE jsontable (
json_column JSON
);
INSERT INTO jsontable VALUES('{"id": 1, "content":"json content"}');
INSERT INTO jsontable VALUES('{"stringdata": "this is a text","number": 1,"numberNeg": -1,"booleanT": true,"booleanGF": false,"nullvalue": null,"object": {"1": 1,"2": 2},"array": [1, 2, 3]}');
INSERT INTO jsontable VALUES(JSON('{"id": 4}'));
SELECT * FROM jsontable;
COL1 |
----------------------------------------------------------------------------------------------------------------------------------------------------+
{"id":1,"content":"json content"} |
{"stringdata":"this is a text","number":1,"numberNeg":-1,"booleanT":true,"booleanGF":false,"nullvalue":null,"object":{"1":1,"2":2},"array":[1,2,3]} |
{"id":4} |
Snowflake¶
CREATE OR REPLACE TABLE jsontable (
json_column VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO jsontable
VALUES('{"id": 1, "content":"json content"}');
INSERT INTO jsontable
VALUES('{"stringdata": "this is a text","number": 1,"numberNeg": -1,"booleanT": true,"booleanGF": false,"nullvalue": null,"object": {"1": 1,"2": 2},"array": [1, 2, 3]}');
INSERT INTO jsontable
VALUES(JSON('{"id": 4}') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'JSON' NODE ***/!!!);
SELECT * FROM
jsontable;
JSON data insertions are not being correctly handled. Check the Recommendationssection for workarounds.
Known Issues¶
1. JSON data insertions
JSON data insertions are not being correctly handled by SnowConvert.
2. JSON objects manipulation
The usages of JSON objects (columns, variables, or parameters) are not correctly converted by SnowConvert. Check the Recommendationssection for workarounds
Recommendations¶
1. JSON Data Type translation workaround¶
JSON datatype is translated to VARIANT, so the information can be formatted using the Snowflake PARSE_JSON function. This approach will allow you to store, query, and operate the JSON data in Snowflake using similar syntax as Oracle.
Oracle¶
CREATE TABLE jsontable (
json_column JSON
);
INSERT INTO jsontable VALUES('{"id": 1, "content":"json content"}');
INSERT INTO jsontable VALUES('{"id": 2, "content": {"header": "header text one", "content": "content text one"}}');
INSERT INTO jsontable VALUES('{"id": 3, "content": {"header": "header tex two", "content": "content text two"}}');
SELECT * FROM jsontable;
SELECT 'ID: ' || jt.json_column.id, 'HEADER: ' || UPPER(jt.json_column.content.header) FROM jsontable jt;
|JSON_SERIALIZE(JSON_COLUMN) |
|----------------------------------------------------------------------------|
|{"id":1,"content":"json content"} |
|{"id":2,"content":{"header":"header text one","content":"content text one"}}|
|{"id":3,"content":{"header":"header tex two","content":"content text two"}} |
|'ID:'||JT.JSON_COLUMN.ID|'HEADER:'||UPPER(JT.JSON_COLUMN.CONTENT.HEADER)|
|------------------------|-----------------------------------------------|
|ID: 1 |HEADER: |
|ID: 2 |HEADER: "HEADER TEXT ONE" |
|ID: 3 |HEADER: "HEADER TEX TWO" |
Snowflake¶
CREATE OR REPLACE TABLE jsontable (
json_column VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO jsontable
VALUES('{"id": 1, "content":"json content"}');
INSERT INTO jsontable
VALUES('{"id": 2, "content": {"header": "header text one", "content": "content text one"}}');
INSERT INTO jsontable
VALUES('{"id": 3, "content": {"header": "header tex two", "content": "content text two"}}');
SELECT * FROM
jsontable;
SELECT 'ID: ' || NVL(jt.json_column.id :: STRING, ''), 'HEADER: ' || NVL(UPPER(jt.json_column.content.header) :: STRING, '') FROM
jsontable jt;
|JSON_COLUMN |
|----------------------------------------------------------------------------------------------------|
|{ "content": "json content", "id": 1} |
|{ "content": { "content": "content text one", "header": "header text one" }, "id": 2}|
|{ "content": { "content": "content text two", "header": "header tex two" }, "id": 3} |
|'ID: ' || JT.JSON_COLUMN:ID|'HEADER: ' || UPPER(JT.JSON_COLUMN:CONTENT:HEADER)| |---------------------------|--------------------------------------------------| |ID: 1 | | |ID: 2 |HEADER: HEADER TEXT ONE | |ID: 3 |HEADER: HEADER TEX TWO ||'ID: ' || JT.JSON_COLUMN:ID|'HEADER: ' || UPPER(JT.JSON_COLUMN:CONTENT:HEADER)|
|---------------------------|--------------------------------------------------|
|ID: 1 | |
|ID: 2 |HEADER: HEADER TEXT ONE |
|ID: 3 |HEADER: HEADER TEX TWO
You must use SELECT as the INSERT INTO argument instead of the VALUES clause to use the PARSE_JSON function.
Use the ‘:’ instead of the ‘.’ operator to access the JSON object properties. It allows several levels of nesting in both engines.
Related EWIs¶
SSC-EWI-0073: Pending Functional Equivalence Review
Extended Data Types¶
Description¶
Beginning with Oracle Database 12_c_, you can specify a maximum size of 32767 bytes for the VARCHAR2, NVARCHAR2, and RAW data types. You can control whether your database supports this new maximum size by setting the initialization parameter MAX_STRING_SIZE.
A VARCHAR2 or NVARCHAR2 data type with a declared size of greater than 4000 bytes, or a RAW data type with a declared size of greater than 2000 bytes, is an extended data type. (Oracle SQL Language Reference Extended Data Type).
Oracle allows increasing the database max string size from STANDARD to EXTENDED, however, Snowflake does not contain an equivalent for this functionality.
Therefore VARCHAR2, NVARCHAR2 and RAW extended Data Types are not supported in Snowflake, and they are transformed just as regular VARCHAR2, NVARCHAR2, and RAW data types. Check Character Data Types and RAW Data Types for more information.
Known Issues¶
1. MAX STRING SIZE not recognized¶
ALTER SYSTEM SET MAX_STRING_SIZE='EXTENDED';
Is not being parsed by SnowConvert.
Related EWIs¶
No related EWIs.
LONG Data Type¶
LONG columns store variable-length character strings containing up to 2 gigabytes -1, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer. (Oracle SQL Language Reference Long Data Type)
Sample Source Patterns¶
Long in Create Table¶
Oracle¶
CREATE TABLE long_table
(
id NUMBER,
long_column LONG
);
INSERT INTO long_table VALUES (1, 'this is a text');
Snowflake¶
CREATE OR REPLACE TABLE long_table
(
id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
long_column VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO long_table
VALUES (1, 'this is a text');
Retrieving data from a Long column¶
Oracle¶
SELECT long_column FROM long_table;
|LONG_COLUMN |
|--------------|
|this is a text|
Snowflake¶
SELECT long_column FROM
long_table;
|LONG_COLUMN |
|--------------|
|this is a text|
Known Issues¶
1. The max length of long (Oracle) and varchar (Snowflake) are different¶
According to Oracle documentation, Long column can store up to 2 gigabytes of data, but Snowflake varchar is limited to 16Mb.
2. Cast of Long column¶
Long data type only can be cast to CLOB data type, and the only way to achieve this is using the TO_LOB function, this function only works if is used in the select list of a subquery in an INSERT statement. Consider the following sample
Oracle¶
CREATE TABLE target_table (col CLOB);
INSERT INTO target_table (SELECT TO_LOB(long_column) FROM long_table);
If the target table column data type is different from CLOB, Oracle may insert null values or display an error when attempting to insert the data.
Related EWIs¶
SSC-FDM-0006: Number type column may not behave similarly in Snowflake
FLOAT Data Type¶
Description¶
The FLOAT data type is a subtype of NUMBER. It can be specified with or without precision, which has the same definition it has forNUMBERand can range from 1 to 126. Scale cannot be specified but is interpreted from the data. (Oracle Language Reference Float Data Type)
Notes on arithmetic operations¶
Please be aware that every operation performed on numerical datatypes is internally stored as a Number. Furthermore, depending on the operation performed it is possible to incur an error related to how intermediate values are stored within Snowflake, for more information please check this post on Snowflake’s post on intermediate numbers in Snowflake.
Sample Source Patterns¶
Please, consider the following table and its inserts for the examples below:
Float data type in Create Table¶
Oracle¶
CREATE TABLE float_data_type_table(
col1 FLOAT,
col2 FLOAT(5),
col3 FLOAT(126)
);
INSERT INTO float_data_type_table (col1) VALUES (100.55555);
INSERT INTO float_data_type_table (col1) VALUES (1.9);
INSERT INTO float_data_type_table (col2) VALUES (1.23);
INSERT INTO float_data_type_table (col2) VALUES (7.89);
INSERT INTO float_data_type_table (col2) VALUES (12.79);
INSERT INTO float_data_type_table (col2) VALUES (123.45);
INSERT INTO float_data_type_table (col3) VALUES (1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111.99999999999999999999555555);
Snowflake¶
CREATE OR REPLACE TABLE float_data_type_table (
col1 FLOAT,
col2 FLOAT(5),
col3 FLOAT(126)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO float_data_type_table(col1) VALUES (100.55555);
INSERT INTO float_data_type_table(col1) VALUES (1.9);
INSERT INTO float_data_type_table(col2) VALUES (1.23);
INSERT INTO float_data_type_table(col2) VALUES (7.89);
INSERT INTO float_data_type_table(col2) VALUES (12.79);
INSERT INTO float_data_type_table(col2) VALUES (123.45);
INSERT INTO float_data_type_table(col3) VALUES (1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111.99999999999999999999555555);
FLOAT¶
There are no differences between Oracle and Snowflake regarding FLOAT data type without precision.
Oracle¶
SELECT col1 FROM float_data_type_table;
col1 |
---------+
100.55555|
1.9|
Snowflake¶
SELECT col1 FROM
float_data_type_table;
col1 |
---------+
100.55555|
1.9|
FLOAT ( p )¶
Queries results may not be equivalent when the precision (p) is specified in theFLOATdata type. There are small rounding differences.
Oracle¶
SELECT col2 FROM float_data_type_table;
SELECT col3 FROM float_data_type_table;
col2|
----+
1.2|
7.9|
13|
120|
col3 |
----------------------------------------------------------------------------------------------------+
1111111111111111111111111111111111111100000000000000000000000000000000000000000000000000000000000000|
Snowflake¶
SELECT col2 FROM
float_data_type_table;
SELECT col3 FROM
float_data_type_table;
col2 |
------+
1.23|
7.89|
12.79|
123.45|
col3 |
----------------------------------------------------------------------------------------------------+
1111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
Known Issues¶
1. FLOAT data type with precision¶
When the FLOAT data type has precision, the queries results may have small rounding differences.
Related EWIs¶
No related EWIs.
NUMBER Data Type¶
Description¶
The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to but not including 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes. (Oracle Language Reference Number Data Type).
The NUMBER data type can be specified using the following form NUMBER(p, s) (both parameters are optional) where:
pis the precision or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. The precision can range from 0 to 38.sis the scale or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.
On Oracle, not specifying precision (using NUMBER or NUMBER(*)) causes the column to be created as an “undefined precision”. This means that Oracle will store values dynamically, allowing to store any number within that column. Snowflake does not support this functionality; for this reason, they will be changed to NUMBER(38, 18), allowing to store the widest variety of numbers.
Notes on arithmetic operations¶
Please be aware that every operation performed on numerical data types is internally stored as a Number. Furthermore, depending on the operation performed it is possible to incur an error related to how intermediate values are stored within Snowflake, for more information please check this post on Snowflake’s post on intermediate numbers in Snowflake or check the functional equivalence message SSC-FDM-0006.
Sample Source Patterns¶
Please, consider the following table and its inserts for the examples below:
Number data types in Create Table¶
Oracle¶
CREATE TABLE number_data_type_table
(
col1 NUMBER,
col2 NUMBER(1),
col3 NUMBER(10, 5),
col4 NUMBER(5, -2),
col5 NUMBER(4, 5)
);
INSERT INTO number_data_type_table(COL1) VALUES(100);
INSERT INTO number_data_type_table(COL2) VALUES(1.99999);
INSERT INTO number_data_type_table(COL3) VALUES(12345.12345);
INSERT INTO number_data_type_table(COL4) VALUES(16430.55555);
INSERT INTO number_data_type_table (COL4) VALUES(17550.55555);
INSERT INTO number_data_type_table(COL5) VALUES(0.00009);
INSERT INTO number_data_type_table(COL5) VALUES(0.000021);
INSERT INTO number_data_type_table(COL5) VALUES(0.012678912);
Snowflake¶
CREATE OR REPLACE TABLE number_data_type_table
(
col1 NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col2 NUMBER(1) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col3 NUMBER(10, 5) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col4 NUMBER(5) !!!RESOLVE EWI!!! /*** SSC-EWI-OR0092 - NUMBER DATATYPE NEGATIVE SCALE WAS REMOVED FROM OUTPUT ***/!!! /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,
col5 NUMBER(5, 5) /*** SSC-FDM-OR0010 - NUMBER DATATYPE SMALLER PRECISION WAS INCREASED TO MATCH SCALE ***/ /*** 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"}}'
;
INSERT INTO number_data_type_table(COL1) VALUES(100);
INSERT INTO number_data_type_table(COL2) VALUES(1.99999);
INSERT INTO number_data_type_table(COL3) VALUES(12345.12345);
INSERT INTO number_data_type_table(COL4) VALUES(16430.55555);
INSERT INTO number_data_type_table(COL4) VALUES(17550.55555);
INSERT INTO number_data_type_table(COL5) VALUES(0.00009);
INSERT INTO number_data_type_table(COL5) VALUES(0.000021);
INSERT INTO number_data_type_table(COL5) VALUES(0.012678912);
NUMBER ( default case )¶
When the precision and the scale are not specified, the default values are the maximum availableNUMBER(38, 127) . The current transformation for the default case is NUMBER(38,19).
In Oracle, not defining Precision nor scale defaults to an “Undefined Precision and Scale”. It behaves by storing the input “as received”, which means it can both deal with Integer and Floating point numbers. We use 38, 18 to try to cover both of them, by using 20 for integers, and leaving 18 for floating-point digits.
Oracle¶
SELECT col1 FROM number_data_type_table;
col1|
----+
100|
Snowflake¶
SELECT col1 FROM
number_data_type_table;
col1 |
-----------------------+
100.0000000000000000000|
NUMBER ( p )¶
In this case, the precision will specify the number of digits that the number could have at the left of the decimal point.
Oracle¶
SELECT col2 FROM number_data_type_table;
col2|
----+
2|
Snowflake¶
SELECT col2 FROM
number_data_type_table;
col2|
----+
2|
NUMBER ( p, s ) p > s¶
In the case where the s is lower than the p, the precision will specify the number of digits that the number could have. The scale will specify the number of significant digits to the right of the decimal point, so the number of digits at the left of the decimal point will depend on the scale specified.
Oracle¶
SELECT col3 FROM number_data_type_table;
col3 |
-----------+
12345.12345|
Snowflake¶
SELECT col3 FROM
number_data_type_table;
col3 |
-----------+
12345.12345|
NUMBER ( p, -s )¶
A negative scale is the number of significant digits to the left of the decimal point, to but not including the least significant digit. For the negative scale, the least significant digit is on the left side of the decimal point, because the actual data is rounded to the specified number of places to the left of the decimal point. The current transformation is to remove the negative scale.
Oracle¶
SELECT col4 FROM number_data_type_table;
col4 |
-----+
16400|
17600|
Snowflake¶
SELECT col4 FROM
number_data_type_table;
col4 |
-----+
16431|
17551|
NUMBER ( p, s ) s > p¶
When the scale is greater than the precision, consider the following aspects:
The number to insert could not have significant digits to the left of the decimal point. Only zero is available.
The first digit to the right of the decimal point must be zero.
The precision specifies the maximum number of significant digits to the right of the decimal point.
Oracle¶
SELECT col5 FROM number_data_type_table;
col5 |
-------+
0.00009|
0.00002|
0.01268|
Snowflake¶
SELECT col5 FROM
number_data_type_table;
col5 |
-------+
0.00009|
0.00002|
0.01268|
Known Issues¶
1. Scale value exceeds the maximum allowed by Snowflake¶
When specifying a scale greater than the maximum allowed in Snowflake (37) it is being changed to 18. To get more information about this please go to the SSC-FDM-0006 documentation.
2. Negative scale¶
Snowflake does not allow negative scale, so it is being removed. This could cause functional inequivalence. To get more information about this issue please go to the SSC-EWI-0R0092 documentation.
Recommendations¶
1. UDF for NUMBER datatype Operations¶
It is possible to migrate these operations manually by using the next UDF when performing arithmetic operations to avoid incurring the issues noted:
CREATE OR REPLACE FUNCTION fixed_divide(a NUMBER(38,19), b NUMBER(38,19))
RETURNS NUMBER(38,19)
LANGUAGE JAVA
CALLED ON NULL INPUT
HANDLER='TestFunc.divide'
AS
'
import java.math.BigDecimal;
import java.math.RoundingMode;
class TestFunc {
public static BigDecimal divide(BigDecimal a, BigDecimal b) {
return a.divide(b,RoundingMode.HALF_UP);
}
}';
Related EWIs¶
SSC-EWI-OR0092 Number datatype negative scale was removed from output.
SSC-FDM-0006: Number type column may not behave similarly in Snowflake
SSC-FDM-OR0010 Number datatype smaller precision was increased to match scale
BINARY_DOUBLE¶
Description¶
BINARY_DOUBLE is a 64-bit, double-precision floating-point number data type. Each BINARY_DOUBLE value requires 8 bytes. In a BINARY_DOUBLE column, floating-point numbers have binary precision. The binary floating-point numbers support the special values infinity and NaN (not a number). (Oracle Language Reference Binary_Double data type)
It is possible to specify floating-point numbers within the next limits:
Maximum positive finite value = 1.79769313486231E+308
Minimum positive finite value = 2.22507485850720E-308
Sample Source Patterns¶
Please, consider the following table and its inserts for the example below:
Binary Double in Create Table¶
Oracle¶
CREATE TABLE binary_double_data_type_table
(
COL1 BINARY_DOUBLE
);
INSERT INTO binary_double_data_type_table VALUES(2.22507485850720E-308D);
INSERT INTO binary_double_data_type_table VALUES(1.79769313486231E+308D);
INSERT INTO binary_double_data_type_table VALUES('NaN');
Snowflake¶
CREATE OR REPLACE TABLE binary_double_data_type_table
(
COL1 FLOAT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO binary_double_data_type_table
VALUES(2.22507485850720E-308);
INSERT INTO binary_double_data_type_table
VALUES(1.79769313486231E+308);
INSERT INTO binary_double_data_type_table
VALUES('NaN');
‘NaN’ means Not a Number, this value is allowed by theBINARY_DOUBLE data type in Oracle and by theFLOATdata type in Snowflake.
BINARY_DOUBLE -> FLOAT¶
Since theBINARY_DOUBLEdata type is not supported by Snowflake it is being converted to FLOAT.
Oracle¶
SELECT * FROM binary_double_data_type_table;
col1 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
0|
179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
NaN|
Snowflake¶
SELECT * FROM
binary_double_data_type_table;
col1 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
0|
179769313486231000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000|
NaN|
Known Issues¶
1. The BINARY_DOUBLE data type is not supported by Snowflake¶
The BINARY_DOUBLE data type is converted to FLOAT since it is not supported by Snowflake.
Related EWIs ¶
No related EWIs.
BINARY_FLOAT¶
Description¶
BINARY_FLOAT is a 32-bit, single-precision floating-point number data type. EachBINARY_FLOATvalue requires 4 bytes. In a BINARY_FLOATcolumn, floating-point numbers have binary precision. The binary floating-point numbers support the special values infinity and NaN (not a number). (Oracle Language Reference Binary_Float data type)
It is possible to specify floating-point numbers within the next limits:
Maximum positive finite value = 3.40282E+38F
Minimum positive finite value = 1.17549E-38F
Sample Source Patterns¶
Please, consider the following table and its inserts for the example below:
Binary Float in Create Table¶
Oracle¶
CREATE TABLE binary_float_data_type_table
(
col1 BINARY_FLOAT
);
INSERT INTO binary_float_data_type_table VALUES(1.17549E-38F);
INSERT INTO binary_float_data_type_table VALUES(3.40282E+38F);
INSERT INTO binary_float_data_type_table VALUES('NaN');
Snowflake¶
CREATE OR REPLACE TABLE binary_float_data_type_table
(
col1 FLOAT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO binary_float_data_type_table
VALUES(1.17549E-38);
INSERT INTO binary_float_data_type_table
VALUES(3.40282E+38);
INSERT INTO binary_float_data_type_table
VALUES('NaN');
‘NaN’ means Not a Number, this value is allowed by theBINARY_FLOAT data type in Oracle and by theFLOATdata type in Snowflake.
BINARY_FLOAT -> FLOAT¶
Since theBINARY_FLOATdata type is not supported by Snowflake it is being converted to FLOAT.
Oracle¶
SELECT * FROM binary_float_data_type_table;
col1 |
---------------------------------------+
0|
340282001837565600000000000000000000000|
NaN|
Snowflake¶
SELECT * FROM binary_float_data_type_table;
col1 |
---------------------------------------+
0|
340282000000000000000000000000000000000|
NaN|
Known Issues¶
1. The BINARY_FLOAT data type is not supported by Snowflake¶
The BINARY_FLOAT data type is converted to FLOAT since it is not supported by Snowflake.
Related EWIs¶
No related EWIs.
TIMESTAMP WITH TIME ZONE Data Type¶
Description¶
TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or a time zone offset in its value. The Snowflake equivalent is TIMESTAMP_TZ.(Oracle SQL Language Reference Timestamp with Time Zone Data Type)
The Snowflake equivalent is TIMESTAMP_TZ.
For more information, see also the TIMESTAMP section.
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
Sample Source Patterns¶
Timestamp with Time Zone in Create Table¶
Oracle¶
CREATE TABLE timestamp_with_time_zone_table
(
timestamp_col1 TIMESTAMP(5) WITH TIME ZONE
);
INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00');
Snowflake¶
CREATE OR REPLACE TABLE timestamp_with_time_zone_table
(
timestamp_col1 TIMESTAMP_TZ(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00');
Retrieving data from a Timestamp with Time Zone column¶
Oracle¶
SELECT * FROM timestamp_with_time_zone_table;
|TIMESTAMP_COL1 |
|-----------------------------|
|2010-10-10 12:00:00.000 -0600|
Snowflake¶
SELECT * FROM
timestamp_with_time_zone_table;
|TIMESTAMP_COL1 |
|-----------------------------|
|2010-10-10 12:00:00.000 -0700|
Note that the timezone is different in both engines because when the timezone is not specified, the default timezone of the database is added.
Use the following syntax to change the default timezone of the database:
ALTER account SET sqtimezone = timezone_string;
Known Issues¶
1. Timestamp formats may be different¶
Snow Convert does not perform any conversion for the date/timestamps format strings, so there may be errors when deploying the code. Example:
Oracle¶
INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');
Snowflake¶
INSERT INTO timestamp_with_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');
The query will fail in Snowflake because the default timestamp input format does not recognize ‘-8:00’ as a valid UTC offset. It should be replaced with ‘-0800’ or ‘-08:00’ in order to get the same result.
Related EWIs¶
No related EWIs.
TIMESTAMP Data Type¶
Description¶
The TIMESTAMP data type is an extension of the DATE data type. It stores the year, month, and day of the DATE data type, plus hour, minute, and second values. (Oracle SQL Language Reference Timestamp Data Type)
Both Oracle and Snowflake TIMESTAMP data types have the same precision range (0-9) but different default values. In Oracle, the default precision value is 6 and in Snowflake is 9.
However, there is a difference in behavior when an inserted value exceeds the set precision. Oracle rounds up the exceeding decimals, while Snowflake just trims the values.
TIMESTAMP [(fractional_seconds_precision)]
Sample Source Patterns¶
Timestamp in Create Table¶
Oracle¶
CREATE TABLE timestamp_table
(
timestamp_col1 TIMESTAMP,
timestamp_col2 TIMESTAMP(7)
);
INSERT INTO timestamp_table(timestamp_col1, timestamp_col2) VALUES (TIMESTAMP '2010-10-10 12:00:00', TIMESTAMP '2010-10-10 12:00:00');
Snowflake¶
CREATE OR REPLACE TABLE timestamp_table
(
timestamp_col1 TIMESTAMP(6),
timestamp_col2 TIMESTAMP(7)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO timestamp_table(timestamp_col1, timestamp_col2) VALUES (TIMESTAMP '2010-10-10 12:00:00', TIMESTAMP '2010-10-10 12:00:00');
Retrieving data from a Timestamp column¶
Oracle¶
SELECT * FROM timestamp_table;
|TIMESTAMP_COL1 |TIMESTAMP_COL2 |
|-----------------------|-----------------------|
|2010-10-10 12:00:00.000|2010-10-10 12:00:00.000|
Snowflake¶
SELECT * FROM
timestamp_table;
|TIMESTAMP_COL1 |TIMESTAMP_COL2 |
|-----------------------|-----------------------|
|2010-10-10 12:00:00.000|2010-10-10 12:00:00.000|
Known Issues¶
No issues were found.
Related EWIs¶
No related EWIs.
TIMESTAMP WITH LOCAL TIME ZONE Data Type¶
Description¶
It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone information is not stored as part of the column data..(Oracle SQL Language Reference Timestamp with Local Time Zone Data Type)
The Snowflake equivalent is TIMESTAMP_LTZ.
For more information, see also the TIMESTAMP section.
TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
Sample Source Patterns¶
Timestamp with Time Zone in Create Table¶
Oracle¶
CREATE TABLE timestamp_with_local_time_zone_table
(
timestamp_col1 TIMESTAMP(5) WITH LOCAL TIME ZONE
);
INSERT INTO timestamp_with_local_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00');
INSERT INTO timestamp_with_local_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -08:00');
Snowflake¶
CREATE OR REPLACE TABLE timestamp_with_local_time_zone_table
(
timestamp_col1 TIMESTAMP_LTZ(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO timestamp_with_local_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00');
INSERT INTO timestamp_with_local_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -08:00');
Retrieving data from a Timestamp with Local Time Zone column¶
Oracle¶
SELECT * FROM timestamp_with_local_time_zone_table;
|TIMESTAMP_COL1 |
|-----------------------|
|2010-10-10 18:00:00.000|
|2010-10-10 20:00:00.000|
Snowflake¶
SELECT * FROM
timestamp_with_local_time_zone_table;
|TIMESTAMP_COL1 |
|-----------------------------|
|2010-10-10 12:00:00.000 -0700|
|2010-10-10 12:00:00.000 -0700|
Note that the results are different in both engines because each database is set with a different time zone. The Oracle timezone is ‘+00:00’ and the Snowflake timezone is ‘America/Los_Angeles’.
Use the following syntax to change the default timezone of the database:
ALTER account SET timezone = timezone_string;
Known Issues¶
1. Default database timezone¶
The operations with this kind of data type will be affected by the database timezone, the results may be different. You can check the default timezone using the following queries:
Oracle¶
SELECT dbtimezone FROM dual;
Snowflake¶
SELECT dbtimezone FROM dual;
2. Oracle Timestamp with local timezone behavior¶
When operating timestamps with local timezone data types, Oracle converts the timestamps to the default timezone of the database. In order to emulate this behavior in Snowflake, the TIMESTAMP_TYPE_MAPPING session parameter should be set to ‘TIMESTAMP_LTZ’.
ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_LTZ';
3. Timestamp formats may be different¶
Snow Convert does not perform any conversion for the date/timestamps format strings, so there may be errors when deploying the code. Example:
Oracle¶
INSERT INTO timestamp_with_local_time_zone_table (timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');
Snowflake¶
INSERT INTO timestamp_with_local_time_zone_table(timestamp_col1) VALUES (TIMESTAMP '2010-10-10 12:00:00 -8:00');
The query will fail in Snowflake because the default timestamp input format does not recognize ‘-8:00’ as a valid UTC offset. It should be replaced with ‘0800’ or ‘-08:00’ in order to get the same result.
Related EWIs¶
No related EWIs.
INTERVAL YEAR TO MONTH Data Type¶
Description¶
INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. There is no equivalent in Snowflake so it is transformed to Varchar (Oracle SQL Language Reference INTERVAL YEAR TO MONTH Data Type)
There is no equivalent for this data type in Snowflake, it is currently transformed to VARCHAR.
INTERVAL YEAR [(year_precision)] TO MONTH
Sample Source Patterns¶
Interval Year To Month in Create Table¶
Oracle¶
CREATE TABLE interval_year_to_month_table
(
interval_year_col1 interval year to month,
interval_year_col2 interval year(4) to month
);
INSERT INTO interval_year_to_month_table(interval_year_col1) VALUES ( INTERVAL '1-2' YEAR TO MONTH );
INSERT INTO interval_year_to_month_table(interval_year_col2) VALUES ( INTERVAL '1000-11' YEAR(4) TO MONTH );
Snowflake¶
CREATE OR REPLACE TABLE interval_year_to_month_table
(
interval_year_col1 VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL year to month DATA TYPE CONVERTED TO VARCHAR ***/!!!,
interval_year_col2 VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL year(4) to month DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO interval_year_to_month_table(interval_year_col1) VALUES ('1y, 2mm');
INSERT INTO interval_year_to_month_table(interval_year_col2) VALUES ('1000y, 11mm');
The Interval value is transformed to a supported Snowflake format and then inserted as text inside the column. Since Snowflake does not support Interval as a data type, it is only supported in arithmetic operations. In order to use the value, it needs to be extracted and used as an Interval constant (if possible).
Original Oracle value: INTERVAL '1-2' YEAR TO MONTH
Value stored in Snowflake column: '1y, 2m'
Value as Snowflake Interval constant: INTERVAL '1y, 2m'
Retrieving data from an Interval Year To Month column¶
Oracle¶
SELECT * FROM interval_year_to_month_table;
|INTERVAL_YEAR_COL1|INTERVAL_YEAR_COL2|
|------------------|------------------|
|1-2 | |
| |1000-11 |
Snowflake¶
SELECT * FROM
interval_year_to_month_table;
INTERVAL_YEAR_COL1|INTERVAL_YEAR_COL2|
------------------+------------------+
1y, 2m | |
|1000y, 11m |
Known Issues¶
1. Only arithmetic operations are supported¶
Snowflake Intervals have several limitations. Only arithmetic operations between DATE or TIMESTAMP and Interval Constants are supported, every other scenario is not supported.
Related EWIs¶
SSC-EWI-0036: Data type converted to another data type.
INTERVAL DAY TO SECOND Data Type¶
Description¶
INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. (Oracle SQL Language Reference INTERVAL DAY TO SECOND Data Type)
There is no equivalent for this data type in Snowflake, it is currently transformed to VARCHAR.
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
Sample Source Patterns¶
Interval Day to Second in Create Table¶
Oracle¶
CREATE TABLE interval_day_to_second_table
(
interval_day_col1 interval day to second,
interval_day_col2 interval day(1) to second(4)
);
INSERT INTO interval_day_to_second_table(interval_day_col1) VALUES ( INTERVAL '1 2:3:4.56' DAY TO SECOND );
INSERT INTO interval_day_to_second_table(interval_day_col2) VALUES ( INTERVAL '1 2:3:4.56' DAY(1) TO SECOND(4) );
Snowflake¶
CREATE OR REPLACE TABLE interval_day_to_second_table
(
interval_day_col1 VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL day to second DATA TYPE CONVERTED TO VARCHAR ***/!!!,
interval_day_col2 VARCHAR(20) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL day(1) to second(4) DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO interval_day_to_second_table(interval_day_col1) VALUES ('1d, 2h, 3m, 4s, 56ms');
INSERT INTO interval_day_to_second_table(interval_day_col2) VALUES ('1d, 2h, 3m, 4s, 56ms');
The Interval value is transformed to a supported Snowflake format and then inserted as text inside the column. Since Snowflake does not support Interval as a data type, it is only supported in arithmetic operations. In order to use the value, it needs to be extracted and used as an Interval constant (if possible).
Original Oracle value: INTERVAL '1 2:3:4.567' DAY TO SECOND
Value stored in Snowflake column: '1d, 2h, 3m, 4s, 567ms'
Value as Snowflake Interval constant: INTERVAL '1d, 2h, 3m, 4s, 567ms'
Retrieving data from an Interval Day to Second column¶
Oracle¶
SELECT * FROM interval_day_to_second_table;
|INTERVAL_DAY_COL1|INTERVAL_DAY_COL2|
|-----------------|-----------------|
|1 2:3:4.567 | |
| |1 2:3:4.567 |
Snowflake¶
SELECT * FROM
interval_day_to_second_table;
INTERVAL_DAY_COL1 |INTERVAL_DAY_COL2 |
--------------------+--------------------+
1d, 2h, 3m, 4s, 56ms| |
|1d, 2h, 3m, 4s, 56ms|
Known Issues¶
1. Only arithmetic operations are supported¶
Snowflake Intervals have several limitations. Only arithmetic operations between DATE or TIMESTAMP and Interval Constants are supported, every other scenario is not supported.
Related EWIs¶
SSC-EWI-0036: Data type converted to another data type.
DATE Data Type¶
Description¶
Oracle’s date data type stores both date and time information, however Snowflake’s date data type only stores date information. (Oracle SQL Language Reference Date Data Type)
The default transformation for Oracle DATE is to Snowflake TIMESTAMP. You can add the disableDateAsTimestamp flag (SnowConvert Command Line Interface) or disable the Transform Date as Timestamp setting (SnowConvert desktop application) in order to transform the DATE type to TIMESTAMP. Keep in mind that Snowflake DATE only stores date information and Oracle stores date and time information, if you want to avoid losing information you should transform DATE to TIMESTAMP.
Sample Source Patterns¶
Date in Create Table¶
Oracle¶
CREATE TABLE date_table
(
date_col date
);
INSERT INTO date_table(date_col) VALUES (DATE '2010-10-10');
Snowflake without –disableDateAsTimestamp flag or with “Transform Date as Timestamp” setting enabled¶
CREATE OR REPLACE TABLE date_table
(
date_col TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
INSERT INTO date_table(date_col) VALUES (DATE '2010-10-10');
Snowflake with –disableDateAsTimestamp flag or with “Transform Date as Timestamp” setting disabled¶
CREATE OR REPLACE TABLE date_table
(
date_col date
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO date_table(date_col) VALUES (DATE '2010-10-10');
Retrieving data from a Date column¶
Oracle¶
SELECT date_col FROM date_table;
|DATE_COL |
|-----------------------|
|2010-10-10 00:00:00.000|
Snowflake¶
SELECT date_col FROM
date_table;
|DATE_COL |
|-----------------------|
|2010-10-10 00:00:00.000|
|DATE_COL |
|----------|
|2010-10-10|
Known Issues¶
1. Input and output format may differ between languages¶
In Snowflake, DATE input and output formats depend on the DATE_INPUT_FORMAT and DATE_OUTPUT_FORMAT session variables. Insertions may fail because the DATE_INPUT_FORMAT enforces the user to use a specific format when a date is added by text. You can modify those variables using the following syntax.
ALTER SESSION SET DATE_INPUT_FORMAT = 'YYYY-DD-MM' DATE_OUTPUT_FORMAT = 'DD-MM-YYYY';
Related EWIs¶
SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior
Interval UDFs vs Snowflake native interval operation¶
Description¶
Necessary Code¶
To run the queries of the comparative table it is necessary to run the following code:
CREATE OR REPLACE TABLE TIMES(
AsTimeStamp TIMESTAMP,
AsTimestampTwo TIMESTAMP,
AsDate DATE,
AsDateTwo DATE
);
INSERT INTO TIMES VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_DATE('06/11/21', 'dd/mm/yy'),
TO_DATE('05/11/21', 'dd/mm/yy'));
CREATE TABLE UNKNOWN_TABLE(
Unknown timestamp
);
INSERT INTO UNKNOWN_TABLE VALUES (
TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.')
);
CREATE OR REPLACE TABLE TIMES (
AsTimeStamp TIMESTAMP(6),
AsTimestampTwo TIMESTAMP(6),
AsDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
AsDateTwo TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
INSERT INTO TIMES
VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_DATE('06/11/21', 'dd/mm/yy'),
TO_DATE('05/11/21', 'dd/mm/yy'));
CREATE OR REPLACE TABLE UNKNOWN_TABLE (
Unknown TIMESTAMP(6)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
INSERT INTO UNKNOWN_TABLE
VALUES (
TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.')
);
Comparison Table¶
SELECT AsTimeStamp+INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1' MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1' MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '2' MONTH FROM TIMES;
SELECT AsTimeStamp-INTERVAL '2' MONTH FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsTimeStamp-INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsTimeStamp+INTERVAL '10' DAY FROM TIMES;
SELECT AsTimeStamp-INTERVAL '10' DAY FROM TIMES;
SELECT AsTimeStamp+INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '5' HOUR FROM TIMES;
SELECT AsTimeStamp-INTERVAL '5' HOUR FROM TIMES;
SELECT AsTimeStamp+INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsTimeStamp-INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsTimeStamp+INTERVAL '30' MINUTE FROM TIMES;
SELECT AsTimeStamp-INTERVAL '30' MINUTE FROM TIMES;
SELECT AsTimeStamp+INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsTimeStamp+INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsTimeStamp-INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsDate+INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsDate-INTERVAL '1-1' YEAR(2) TO MONTH FROM TIMES;
SELECT AsDate+INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsDate-INTERVAL '2-1' YEAR(4) TO MONTH FROM TIMES;
SELECT AsDate+INTERVAL '1' MONTH FROM TIMES;
SELECT AsDate-INTERVAL '1' MONTH FROM TIMES;
SELECT AsDate+INTERVAL '2' MONTH FROM TIMES;
SELECT AsDate-INTERVAL '2' MONTH FROM TIMES;
SELECT AsDate+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsDate-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM TIMES;
SELECT AsDate+INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '1 01:10' DAY TO MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsDate-INTERVAL '1 1' DAY TO HOUR FROM TIMES;
SELECT AsDate+INTERVAL '10' DAY FROM TIMES;
SELECT AsDate-INTERVAL '10' DAY FROM TIMES;
SELECT AsDate+INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '3:05' HOUR TO MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '5' HOUR FROM TIMES;
SELECT AsDate-INTERVAL '5' HOUR FROM TIMES;
SELECT AsDate+INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsDate-INTERVAL '5:10' MINUTE TO SECOND FROM TIMES;
SELECT AsDate+INTERVAL '30' MINUTE FROM TIMES;
SELECT AsDate-INTERVAL '30' MINUTE FROM TIMES;
SELECT AsDate+INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsDate-INTERVAL '333' HOUR(3) FROM TIMES;
SELECT AsDate+INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT AsDate-INTERVAL '15.6789' SECOND(2,3) FROM TIMES;
SELECT Unknown+INTERVAL '1-1' YEAR(2) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1-1' YEAR(2) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '2-1' YEAR(4) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '2-1' YEAR(4) TO MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '2' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '2' MONTH FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 01:00:00.222' DAY TO SECOND(3) FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 01:10' DAY TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 01:10' DAY TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '1 1' DAY TO HOUR FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '1 1' DAY TO HOUR FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '10' DAY FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '10' DAY FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '3:05' HOUR TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '3:05' HOUR TO MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '5' HOUR FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '5' HOUR FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '5:10' MINUTE TO SECOND FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '5:10' MINUTE TO SECOND FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '30' MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '30' MINUTE FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '333' HOUR(3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '333' HOUR(3) FROM UNKNOWN_TABLE;
SELECT Unknown+INTERVAL '15.6789' SECOND(2,3) FROM UNKNOWN_TABLE;
SELECT Unknown-INTERVAL '15.6789' SECOND(2,3) FROM UNKNOWN_TABLE;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+ AsTimeStamp FROM TIMES;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+AsDate FROM TIMES;
SELECT INTERVAL '1-1' YEAR(2) TO MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+AsDate FROM TIMES;
SELECT INTERVAL '2-1' YEAR(4) TO MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1' MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1' MONTH+AsDate FROM TIMES;
SELECT INTERVAL '1' MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '2' MONTH+AsTimeStamp FROM TIMES;
SELECT INTERVAL '2' MONTH+AsDate FROM TIMES;
SELECT INTERVAL '2' MONTH+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+AsDate FROM TIMES;
SELECT INTERVAL '1 01:00:00.222' DAY TO SECOND(3)+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '1 01:10' DAY TO MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '1 1' DAY TO HOUR+AsTimeStamp FROM TIMES;
SELECT INTERVAL '1 1' DAY TO HOUR+AsDate FROM TIMES;
SELECT INTERVAL '1 1' DAY TO HOUR+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '10' DAY+AsTimeStamp FROM TIMES;
SELECT INTERVAL '10' DAY+AsDate FROM TIMES;
SELECT INTERVAL '10' DAY+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '3:05' HOUR TO MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '3:05' HOUR TO MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '3:05' HOUR TO MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '5' HOUR+AsTimeStamp FROM TIMES;
SELECT INTERVAL '5' HOUR+AsDate FROM TIMES;
SELECT INTERVAL '5' HOUR+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '5:10' MINUTE TO SECOND+AsTimeStamp FROM TIMES;
SELECT INTERVAL '5:10' MINUTE TO SECOND+AsDate FROM TIMES;
SELECT INTERVAL '5:10' MINUTE TO SECOND+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '30' MINUTE+AsTimeStamp FROM TIMES;
SELECT INTERVAL '30' MINUTE+AsDate FROM TIMES;
SELECT INTERVAL '30' MINUTE+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '333' HOUR(3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '333' HOUR(3)+AsDate FROM TIMES;
SELECT INTERVAL '333' HOUR(3)+Unknown FROM UNKNOWN_TABLE;
SELECT INTERVAL '15.6789' SECOND(2,3)+AsTimeStamp FROM TIMES;
SELECT INTERVAL '15.6789' SECOND(2,3)+AsDate FROM TIMES;
SELECT INTERVAL '15.6789' SECOND(2,3)+Unknown FROM UNKNOWN_TABLE;
SELECT AsTimeStamp + INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsTimeStamp + INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT AsTimeStamp - INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '2y, 1mm' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1 month' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '2 month' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1d, 01h, 10m' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '1d, 1h' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '10 day' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '3h, 05m' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '5 hour' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '5m, 10s' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '30 minute' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '333 hour' FROM PUBLIC.TIMES;
SELECT AsDate + INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT AsDate - INTERVAL '15.6789 second' FROM PUBLIC.TIMES;
SELECT Unknown + INTERVAL '1y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '2y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '2y, 1mm' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '2 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '2 month' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1d, 01h, 00m, 00s, 222ms' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1d, 01h, 10m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1d, 01h, 10m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '1d, 1h' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '1d, 1h' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '10 day' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '10 day' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '3h, 05m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '3h, 05m' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '5 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '5 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '5m, 10s' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '5m, 10s' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '30 minute' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '30 minute' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '333 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '333 hour' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown + INTERVAL '15.6789 second' FROM PUBLIC.UNKNOWN_TABLE;
SELECT Unknown - INTERVAL '15.6789 second' FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsTimeStamp,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsTimeStamp,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''2'' MONTH') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''10'' DAY') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''5'' HOUR') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''30'' MINUTE') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(AsDate,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEDIFF_UDF(AsDate,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.TIMES;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1-1'' YEAR(2) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''2-1'' YEAR(4) TO MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''2'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''2'' MONTH') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1 01:10'' DAY TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''1 1'' DAY TO HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''10'' DAY') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''10'' DAY') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''3:05'' HOUR TO MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''5'' HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''5'' HOUR') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''5:10'' MINUTE TO SECOND') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''30'' MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''30'' MINUTE') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''333'' HOUR(3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEADD_UDF(UnKnown,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.UNKNOWN_TABLE;
SELECT DATEDIFF_UDF(UnKnown,'INTERVAL ''15.6789'' SECOND(2,3)') FROM PUBLIC.UNKNOWN_TABLE;
| Oracle | Snowflake Operation | UDF |
|---|---|---|
| 2022-12-05 11:00:00.000 | 2022-12-05 11:00:00.000 | 2022-12-05 11:00:00.000 |
| 2020-10-05 11:00:00.000 | 2020-10-05 11:00:00.000 | 2020-10-05 11:00:00.000 |
| 2023-12-05 11:00:00.000 | 2023-12-05 11:00:00.000 | 2023-12-05 11:00:00.000 |
| 2019-10-05 11:00:00.000 | 2019-10-05 11:00:00.000 | 2019-10-05 11:00:00.000 |
| 2021-12-05 11:00:00.000 | 2021-12-05 11:00:00.000 | 2021-12-05 11:00:00.000 |
| 2021-10-05 11:00:00.000 | 2021-10-05 11:00:00.000 | 2021-10-05 11:00:00.000 |
| 2022-01-05 11:00:00.000 | 2022-01-05 11:00:00.000 | 2022-01-05 11:00:00.000 |
| 2021-09-05 11:00:00.000 | 2021-09-05 11:00:00.000 | 2021-09-05 11:00:00.000 |
| 2021-11-06 12:00:00.222 | 2021-11-06 12:00:00.222 | 2021-11-06 12:00:00.222 |
| 2021-11-04 09:59:59.778 | 2021-11-04 09:59:59.778 | 2021-11-04 09:59:59.778 |
| 2021-11-06 12:10:00.000 | 2021-11-06 12:10:00.000 | 2021-11-06 12:10:00.000 |
| 2021-11-04 09:50:00.000 | 2021-11-04 09:50:00.000 | 2021-11-04 09:50:00.000 |
| 2021-11-06 12:00:00.000 | 2021-11-06 12:00:00.000 | 2021-11-06 12:00:00.000 |
| 2021-11-04 10:00:00.000 | 2021-11-04 10:00:00.000 | 2021-11-04 10:00:00.000 |
| 2021-11-15 11:00:00.000 | 2021-11-15 11:00:00.000 | 2021-11-15 11:00:00.000 |
| 2021-10-26 11:00:00.000 | 2021-10-26 11:00:00.000 | 2021-10-26 11:00:00.000 |
| 2021-11-05 14:05:00.000 | 2021-11-05 14:05:00.000 | 2021-11-05 14:05:00.000 |
| 2021-11-05 07:55:00.000 | 2021-11-05 07:55:00.000 | 2021-11-05 07:55:00.000 |
| 2021-11-05 16:00:00.000 | 2021-11-05 16:00:00.000 | 2021-11-05 16:00:00.000 |
| 2021-11-05 06:00:00.000 | 2021-11-05 06:00:00.000 | 2021-11-05 06:00:00.000 |
| 2021-11-05 11:05:10.000 | 2021-11-05 11:05:10.000 | 2021-11-05 11:05:10.000 |
| 2021-11-05 10:54:50.000 | 2021-11-05 10:54:50.000 | 2021-11-05 10:54:50.000 |
| 2021-11-05 11:30:00.000 | 2021-11-05 11:30:00.000 | 2021-11-05 11:30:00.000 |
| 2021-11-05 10:30:00.000 | 2021-11-05 10:30:00.000 | 2021-11-05 10:30:00.000 |
| 2021-11-19 08:00:00.000 | 2021-11-19 08:00:00.000 | 2021-11-19 08:00:00.000 |
| 2021-10-22 14:00:00.000 | 2021-10-22 14:00:00.000 | 2021-10-22 14:00:00.000 |
| 2021-11-05 11:00:15.679 | 2021-11-05 11:00:16.000 | 2021-11-05 11:00:15.678 |
| 2021-11-05 10:59:44.321 | 2021-11-05 10:59:44.000 | 2021-11-05 11:00:15.678 |
| 2022-12-06 00:00:00.000 | 2022-12-06 | 2022-12-06 |
| 2020-10-06 00:00:00.000 | 2020-10-06 | 2020-10-06 |
| 2023-12-06 00:00:00.000 | 2023-12-06 | 2023-12-06 |
| 2019-10-06 00:00:00.000 | 2019-10-06 | 2019-10-06 |
| 2021-12-06 00:00:00.000 | 2021-12-06 | 2021-12-06 |
| 2021-12-06 00:00:00.000 | 2021-10-06 | 2021-10-06 |
| 2022-01-06 00:00:00.000 | 2022-01-06 | 2022-01-06 |
| 2021-09-06 00:00:00.000 | 2021-09-06 | 2021-09-06 |
| 2021-11-07 01:00:00.000 | 2021-11-07 01:00:00.222 | 2021-11-07 |
| 2021-11-04 22:59:59.000 | 2021-11-04 22:59:59.778 | 2021-11-04 |
| 2021-11-07 01:10:00.000 | 2021-11-07 01:10:00.000 | 2021-11-07 |
| 2021-11-04 22:50:00.000 | 2021-11-04 22:50:00.000 | 2021-11-04 |
| 2021-11-07 01:00:00.000 | 2021-11-07 01:00:00.000 | 2021-11-07 |
| 2021-11-04 23:00:00.000 | 2021-11-04 23:00:00.000 | 2021-11-04 |
| 2021-11-16 00:00:00.000 | 2021-11-16 | 2021-11-16 |
| 2021-10-27 00:00:00.000 | 2021-10-27 | 2021-10-27 |
| 2021-11-06 03:05:00.000 | 2021-11-06 03:05:00.000 | 2021-11-06 |
| 2021-11-05 20:55:00.000 | 2021-11-05 20:55:00.000 | 2021-11-05 |
| 2021-11-06 05:00:00.000 | 2021-11-06 05:00:00.000 | 2021-11-06 |
| 2021-11-05 19:00:00.000 | 2021-11-05 19:00:00.000 | 2021-11-05 |
| 2021-11-06 00:05:10.000 | 2021-11-06 00:05:10.000 | 2021-11-06 |
| 2021-11-05 23:54:50.000 | 2021-11-05 23:54:50.000 | 2021-11-05 |
| 2021-11-06 00:30:00.000 | 2021-11-06 00:30:00.000 | 2021-11-06 |
| 2021-11-05 23:30:00.000 | 2021-11-05 23:30:00.000 | 2021-11-05 |
| 2021-11-19 21:00:00.000 | 2021-11-19 21:00:00.000 | 2021-11-19 |
| 2021-10-23 03:00:00.000 | 2021-10-23 03:00:00.000 | 2021-10-23 |
| 2021-11-06 00:00:15.000 | 2021-11-06 00:00:16.000 | 2021-11-06 |
| 2021-11-05 23:59:44.000 | 2021-11-05 23:59:44.000 | 2021-11-05 |
| 2010-11-01 12:00:00.000 | 2010-11-01 12:00:00.000 | 2010-11-01 12:00:00.000 |
| 2008-09-01 12:00:00.000 | 2008-09-01 12:00:00.000 | 2008-09-01 12:00:00.000 |
| 2011-11-01 12:00:00.000 | 2011-11-01 12:00:00.000 | 2011-11-01 12:00:00.000 |
| 2007-09-01 12:00:00.000 | 2007-09-01 12:00:00.000 | 2007-09-01 12:00:00.000 |
| 2009-11-01 12:00:00.000 | 2009-11-01 12:00:00.000 | 2009-11-01 12:00:00.000 |
| 2009-09-01 12:00:00.000 | 2009-09-01 12:00:00.000 | 2009-09-01 12:00:00.000 |
| 2009-12-01 12:00:00.000 | 2009-12-01 12:00:00.000 | 2009-12-01 12:00:00.000 |
| 2009-08-01 12:00:00.000 | 2009-08-01 12:00:00.000 | 2009-08-01 12:00:00.000 |
| 2009-10-02 13:00:00.222 | 2009-10-02 13:00:00.222 | 2009-10-02 13:00:00.222 |
| 2009-09-30 10:59:59.778 | 2009-09-30 10:59:59.778 | 2009-09-30 10:59:59.778 |
| 2009-10-02 13:10:00.000 | 2009-10-02 13:10:00.000 | 2009-10-02 13:10:00.000 |
| 2009-09-30 10:50:00.000 | 2009-09-30 10:50:00.000 | 2009-09-30 10:50:00.000 |
| 2009-10-02 13:00:00.000 | 2009-10-02 13:00:00.000 | 2009-10-02 13:00:00.000 |
| 2009-09-30 11:00:00.000 | 2009-09-30 11:00:00.000 | 2009-09-30 11:00:00.000 |
| 2009-10-11 12:00:00.000 | 2009-10-11 12:00:00.000 | 2009-10-11 12:00:00.000 |
| 2009-09-21 12:00:00.000 | 2009-09-21 12:00:00.000 | 2009-09-21 12:00:00.000 |
| 2009-10-01 15:05:00.000 | 2009-10-01 15:05:00.000 | 2009-10-01 15:05:00.000 |
| 2009-10-01 08:55:00.000 | 2009-10-01 08:55:00.000 | 2009-10-01 08:55:00.000 |
| 2009-10-01 17:00:00.000 | 2009-10-01 17:00:00.000 | 2009-10-01 17:00:00.000 |
| 2009-10-01 07:00:00.000 | 2009-10-01 07:00:00.000 | 2009-10-01 07:00:00.000 |
| 2009-10-01 12:05:10.000 | 2009-10-01 12:05:10.000 | 2009-10-01 12:05:10.000 |
| 2009-10-01 11:54:50.000 | 2009-10-01 11:54:50.000 | 2009-10-01 11:54:50.000 |
| 2009-10-01 12:30:00.000 | 2009-10-01 12:30:00.000 | 2009-10-01 12:30:00.000 |
| 2009-10-01 11:30:00.000 | 2009-10-01 11:30:00.000 | 2009-10-01 11:30:00.000 |
| 2009-10-15 09:00:00.000 | 2009-10-15 09:00:00.000 | 2009-10-15 09:00:00.000 |
| 2009-09-17 15:00:00.000 | 2009-09-17 15:00:00.000 | 2009-09-17 15:00:00.000 |
| 2009-10-01 12:00:15.679 | 2009-10-01 12:00:16.000 | 2009-10-01 12:00:15.678 |
| 2009-10-01 11:59:44.321 | 2009-10-01 11:59:44.000 | 2009-10-01 11:59:44.321 |
Known Issues¶
No issues were found.
Related EWIs¶
SSC-FDM-OR0042: Date Type Transformed To Timestamp Has A Different Behavior
BINARY_INTEGER Data Type¶
This data type is identical to the PLS_INTEGER data type.
PLS_INTEGER Data Type¶
Description¶
The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits. (Oracle Language Reference PLS_INTEGER Data Type)
The PLS_INTEGER datatype is transformed to NUMBER. This transformation also applies for each PLS_INTEGER subtype:
NATURALNATURALNPOSITIVEPOSITIVENSIGNTYPESIMPLE_INTEGER
Some of these subtypes are currently not recognized by SnowConvert so they are converted to VARIANT and considered user-defined types. There is already a work item to fix the issue.
Sample Source Patterns¶
Please, consider the following table and its inserts for the examples below:
CREATE TABLE PLS_INTEGER_TABLE(
COL NUMBER
);
PLS_INTEGER usage in procedural blocks¶
Oracle¶
CREATE OR REPLACE PROCEDURE PLS_INTEGER_EXAMPLE
IS
-- PLS_INTEGER AND BINARY INTEGER ALIASES
PLS_INTEGER_VAR PLS_INTEGER;
BINARY_INTEGER_VAR BINARY_INTEGER;
NUMBER_VAR NUMBER;
BEGIN
NUMBER_VAR := 2;
-- maximum possible value
PLS_INTEGER_VAR := 2147483647;
-- implicit cast to number
INSERT INTO PLS_INTEGER_TABLE (COL) VALUES (PLS_INTEGER_VAR);
PLS_INTEGER_VAR := 2147483647;
-- operations with other numeric expressions
INSERT INTO PLS_INTEGER_TABLE (COL) VALUES (PLS_INTEGER_VAR + 1);
INSERT INTO PLS_INTEGER_TABLE (COL) VALUES (PLS_INTEGER_VAR + NUMBER_VAR);
END;
CALL PLS_INTEGER_EXAMPLE();
SELECT * FROM PLS_INTEGER_TABLE;
|COL |
|----------|
|2147483647|
|2147483648|
|2147483649|
Snowflake¶
CREATE OR REPLACE PROCEDURE PLS_INTEGER_EXAMPLE ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
DECLARE
-- PLS_INTEGER AND BINARY INTEGER ALIASES
PLS_INTEGER_VAR NUMBER;
BINARY_INTEGER_VAR NUMBER;
NUMBER_VAR NUMBER(38, 18);
BEGIN
NUMBER_VAR := 2;
-- maximum possible value
PLS_INTEGER_VAR := 2147483647;
-- implicit cast to number
INSERT INTO PLS_INTEGER_TABLE(COL) VALUES (:PLS_INTEGER_VAR);
PLS_INTEGER_VAR := 2147483647;
-- operations with other numeric expressions
INSERT INTO PLS_INTEGER_TABLE(COL) VALUES (:PLS_INTEGER_VAR + 1);
INSERT INTO PLS_INTEGER_TABLE(COL) VALUES (:PLS_INTEGER_VAR + :NUMBER_VAR);
END;
$$;
CALL PLS_INTEGER_EXAMPLE();
SELECT * FROM
PLS_INTEGER_TABLE;
|COL |
|----------|
|2147483647|
|2147483648|
|2147483649|
Known Issues¶
1. Storage and performance features were not preserved¶
Oracle PLS_INTEGER has some advantages in terms of storage size and performance in arithmetic operations. These features were not emulated because Snowflake NUMBER does not have them. For more information, check the PLS_INTEGER documentation.
Related EWIs ¶
No related EWIs.
VARCHAR2 Data Type¶
Description¶
The VARCHAR2 data type specifies a variable-length character string in the database character set. (Oracle SQL Language Reference VARCHAR2)
As denoted in the Oracle documentation, size in VARCHAR2 data type is a length constraint and should not be confused with capacity. Total characters that can be stored in a VARCHAR2 may vary according to the database character set and configuration, but commonly the maximum size allowed is 4000.
VARCHAR2 is translated to Snowflake VARCHAR which can store a bigger number of bytes/characters by default. Either way, the memory used is variable using the size of the value stored in the column as same as in Oracle.
VARCHAR2 (size [ BYTE | CHAR ])
Sample Source Patterns¶
Varchar2 data types in Create Table¶
Oracle¶
CREATE TABLE varchar2_data_types
(
varchar2_column1 VARCHAR2(5),
varchar2_column2 VARCHAR2(5 BYTE),
varchar2_column3 VARCHAR2(5 CHAR)
);
INSERT INTO varchar2_data_types VALUES ('H', 'Hello', 'Hell');
Snowflake¶
CREATE OR REPLACE TABLE varchar2_data_types
(
varchar2_column1 VARCHAR(5),
varchar2_column2 VARCHAR(5),
varchar2_column3 VARCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO varchar2_data_types
VALUES ('H', 'Hello', 'Hell');
Retrieving data from varchar columns¶
Oracle¶
SELECT * FROM varchar2_data_types;
VARCHAR2_COLUMN1|VARCHAR2_COLUMN2|VARCHAR2_COLUMN3|
----------------+----------------+----------------+
H |Hello |Hell |
Snowflake¶
SELECT * FROM
varchar2_data_types;
VARCHAR2_COLUMN1|VARCHAR2_COLUMN2|VARCHAR2_COLUMN3|
----------------+----------------+----------------+
H |Hello |Hell |
Reviewing the variable size in the columns¶
Oracle¶
SELECT
LENGTHB(varchar2_column1),
LENGTHB(varchar2_column2),
LENGTHB(varchar2_column3)
FROM VARCHAR2_DATA_TYPES;
LENGTHB(VARCHAR2_COLUMN1)|LENGTHB(VARCHAR2_COLUMN2)|LENGTHB(VARCHAR2_COLUMN3)|
-------------------------+-------------------------+-------------------------+
1| 5| 4|
Snowflake¶
SELECT
OCTET_LENGTH(varchar2_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(varchar2_column2) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(varchar2_column3) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
VARCHAR2_DATA_TYPES;
OCTET_LENGTH(VARCHAR2_COLUMN1)|OCTET_LENGTH(VARCHAR2_COLUMN2)|OCTET_LENGTH(VARCHAR2_COLUMN3)|
------------------------------+------------------------------+------------------------------+
1| 5| 4|
Known Issues¶
No issues were found.
Related EWIs¶
SSC-FDM-OR0015: LENGTHB transformed to OCTET_LENGTH.
CHAR Data type¶
Description¶
The CHAR data type specifies a fixed-length character string in the database character set.(Oracle SQL Language Reference CHAR Data type)
As denoted in the Oracle documentation, size in CHAR data type is a length constraint and should not be confused with capacity. Total characters that can be stored in a CHAR may vary according to the database character set and configuration, but commonly the maximum size allowed is 2000.
In Snowflake, CHAR types are synonymous with VARCHAR, and as you can check here:
Snowflake SQL Language reference text data types
The standard maximum size is quite bigger. But, this doesn’t mean that a Snowflake VARCHAR will consume more storage, as mentioned in their documentation:
A 1-character string in a VARCHAR(16777216) column only consumes a single character.
CHAR [ (size [ BYTE | CHAR ])
Sample Source Patterns¶
Char data types in Create Table¶
Oracle¶
CREATE TABLE char_data_types
(
char_column1 CHAR,
char_column2 CHAR(15),
char_column3 CHAR(15 BYTE),
char_column4 CHAR(15 CHAR)
);
INSERT INTO char_data_types VALUES ('H', 'Hello world', 'Hello world', 'Hello world');
Snowflake¶
CREATE OR REPLACE TABLE char_data_types
(
char_column1 CHAR,
char_column2 CHAR(15),
char_column3 CHAR(15),
char_column4 CHAR(15)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO char_data_types
VALUES ('H', 'Hello world', 'Hello world', 'Hello world');
Retrieving data from char columns¶
Oracle¶
SELECT * FROM char_data_types;
CHAR_COLUMN1|CHAR_COLUMN2 |CHAR_COLUMN3 |CHAR_COLUMN4 |
------------+---------------+---------------+---------------+
H |Hello world |Hello world |Hello world |
Snowflake¶
SELECT * FROM
char_data_types;
CHAR_COLUMN1|CHAR_COLUMN2|CHAR_COLUMN3|CHAR_COLUMN4|
------------+------------+------------+------------+
H |Hello world |Hello world |Hello world |
In Oracle, the value is filled with empty spaces to fit the fixed size determined in the column definition. On the other hand, Snowflakes uses dynamic size (keeping the length restriction) to store the value.
Checking internal data types for CHAR¶
As mentioned in the beginning, Snowflake internally uses a VARCHAR for the CHAR type columns, we can confirm it by describing the tables:
Oracle¶
Snowflake¶
The length restriction is preserved, but the memory that the columns are using is different on each DBMS.
Retrieving the size in bytes of each column¶
Oracle¶
SELECT
LENGTHB(char_column1),
LENGTHB(char_column2),
LENGTHB(char_column3),
LENGTHB(char_column4)
FROM char_data_types;
LENGTHB(CHAR_COLUMN1)|LENGTHB(CHAR_COLUMN2)|LENGTHB(CHAR_COLUMN3)|LENGTHB(CHAR_COLUMN4)|
---------------------+---------------------+---------------------+---------------------+
1| 15| 15| 15|
Snowflake¶
SELECT
OCTET_LENGTH(char_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(char_column2) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(char_column3) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(char_column4) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
char_data_types;
OCTET_LENGTH(CHAR_COLUMN1)|OCTET_LENGTH(CHAR_COLUMN2)|OCTET_LENGTH(CHAR_COLUMN3)|OCTET_LENGTH(CHAR_COLUMN4)|
--------------------------+--------------------------+--------------------------+--------------------------+
1| 11| 11| 11|
Besides these slight differences, the integrity of the data is preserved.
Known Issues¶
1. Results obtained from some built-in functions may vary
As explained in the previous section, there may be cases using built-in functions over the columns that may retrieve different results. For example, get the length of a column.
Related EWIs¶
SSC-FDM-OR0015: LENGTHB transformed to OCTET_LENGTH.
NVARCHAR2 Data Type¶
Description¶
The NVARCHAR2 data type specifies a variable-length character string in the national character set. (Oracle SQL Language Reference NVARCHAR2)
NVARCHAR2 (size)
NVARCHAR2 allows to store special characters with their Unicode to be preserved across any usage, these special characters may need more bits to be stored and that is why, by default, the NVARCHAR2 character set is AL16UTF16, contrary to the common character data set for VARCHAR2 which is usually AL32UTF8.
NVARCHAR transformed to Snowflake VARCHAR, Transformation information related to VARCHAR2, is also valid for NVARCHAR2.
NVARCHAR2 (size)
Sample Souce Patterns¶
Nvarchar2 data type in Create Table¶
Oracle¶
CREATE TABLE nvarchar2_data_types
(
nvarchar2_column NVARCHAR2 (5)
);
INSERT INTO nvarchar2_data_types VALUES ('ភាសាខ');
Snowflake¶
CREATE OR REPLACE TABLE nvarchar2_data_types
(
nvarchar2_column VARCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO nvarchar2_data_types
VALUES ('ភាសាខ');
In Oracle, trying to insert these values in a VARCHAR2 column with the same size, will trigger an error: value too large for column.
Retrieving information from Nchar columns¶
Oracle¶
SELECT * FROM nvarchar2_data_types;
NVARCHAR2_COLUMN
----------------
ភាសាខ |
Snowflake¶
SELECT * FROM
nvarchar2_data_types;
NVARCHAR2_COLUMN|
----------------+
ភាសាខ |
Retrieving the size in bytes of each column¶
Oracle¶
SELECT
LENGTHB(nvarchar2_column)
FROM nvarchar2_data_types;
LENGTHB(NVARCHAR2_COLUMN)|
-------------------------+
10|
Snowflake¶
SELECT
OCTET_LENGTH(nvarchar2_column) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
nvarchar2_data_types;
OCTET_LENGTH(NVARCHAR2_COLUMN)|
------------------------------+
15|
Note that the number specified in the column declaration is the size in characters and not in bytes, That is why we see more space used to store those special characters.
In Snowflake, VARCHAR uses UTF-8, size can vary depending on the Unicode character that can be represented in 1, 2, 3, or 4 bytes. In this case, the Cambodian characters are using 3 bytes to be stored.
Besides these slight differences, the integrity of the data is preserved.
Known Issues¶
1. Results obtained from some built-in functions may vary
As explained in the previous section, there may be cases using built-in functions over the columns that may retrieve different results. For example, get the length of a column.
Related EWIs¶
SSC-FDM-OR0015: LENGTHB transformed to OCTET_LENGTH.
VARCHAR Data Type¶
Description¶
Oracle recommends using VARCHAR2 instead of VARCHAR as explained in their documentation:
Oracle SQL Language reference Varchar
Even though, the syntaxis is parsed and transformed using the ANSI, DB2, and SQL/DS Data Types.
NCHAR Data Type¶
Description¶
The NCHAR data type specifies a fixed-length character string in the national character set. (Oracle SQL Language Reference NCHAR)
NCHAR allows to store special characters with their Unicode to be preserved across any usage, these special characters may need more bits to be stored and that is why, by default, the NCHAR character set is AL16UTF16, contrary to the common character data set for CHAR which is usually AL32UTF8.
NCHAR is preserved as NCHAR in Snowflake, but, in the background, Snowflake uses VARCHAR. Transformation information related to CHAR is also valid for NCHAR.
NCHAR [ (size) ]
Sample Souce Patterns¶
Nchar data types in Create Table¶
Oracle¶
CREATE TABLE nchar_data_types
(
nchar_column1 NCHAR,
nchar_column2 NCHAR(5)
);
INSERT INTO nchar_data_types VALUES ('ភ', 'ភាសាខ');
Snowflake¶
CREATE OR REPLACE TABLE nchar_data_types
(
nchar_column1 NCHAR,
nchar_column2 NCHAR(5)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO nchar_data_types
VALUES ('ភ', 'ភាសាខ');
In Oracle, trying to insert these values in a CHAR column with the same size, will trigger an error: value too large for column.
Retrieving information from Nchar columns¶
Oracle¶
SELECT * FROM nchar_data_types;
NCHAR_COLUMN1|NCHAR_COLUMN2|
-------------+-------------+
ភ |ភាសាខ |
Snowflake¶
SELECT * FROM
nchar_data_types;
NCHAR_COLUMN1|NCHAR_COLUMN2|
-------------+-------------+
ភ |ភាសាខ |
Retrieving the size in bytes of each column¶
Oracle¶
SELECT
LENGTHB(nchar_column1),
LENGTHB(nchar_column2)
FROM nchar_data_types;
LENGTHB(NCHAR_COLUMN1)|LENGTHB(NCHAR_COLUMN2)|
----------------------+----------------------+
2| 10|
Snowflake¶
SELECT
OCTET_LENGTH(nchar_column1) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/,
OCTET_LENGTH(nchar_column2) /*** SSC-FDM-OR0015 - LENGTHB TRANSFORMED TO OCTET_LENGTH RESULTS MAY VARY DUE TO MEMORY MANAGEMENT OF DBMS ***/
FROM
nchar_data_types;
OCTET_LENGTH(NCHAR_COLUMN1)|OCTET_LENGTH(NCHAR_COLUMN2)|
---------------------------+---------------------------+
3| 15|
Note that the number specified in the column declaration is the size in characters and not in bytes, That is why we see more space used to store those special characters.
In Snowflake, VARCHAR uses UTF-8, size can vary depending on the Unicode character that can be represented in 1, 2, 3, or 4 bytes. In this case, the Cambodian character is using 3 bytes to be stored.
Besides these slight differences, the integrity of the data is preserved.
Known Issues¶
1. Results obtained from some built-in functions may vary
As explained in the previous section, there may be cases using built-in functions over the columns that may retrieve different results. For example, get the length of a column.
Related EWIs¶
SSC-FDM-OR0015: LENGTHB transformed to OCTET_LENGTH.
BFILE Data Type¶
Description¶
Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. A BFILE column or attribute stores a BFILE locator, which serves as a pointer to a binary file on the server file system. The locator maintains the directory name and the filename. (Oracle SQL Language Reference BFILE Data Type).
BFILE Data Type is not supported in Snowflake. VARCHAR is used instead.
Sample Source Patterns¶
Bfile data type in Create Table¶
Oracle BFILE columns are used to store a locator with the directory and filename. They are changed to Snowflake VARCHAR in order to store the directory and filename into the column. However, loading the content of the file must be done manually.
Oracle¶
--Create Table
CREATE TABLE bfile_table
(
col1 BFILE
);
--Insert Bfilename
INSERT INTO bfile_table VALUES (
BFILENAME('mydirectory', 'myfile.png')
);
--Select
SELECT * FROM bfile_table;
COL1 |
------------------+
[BFILE:myfile.png]|
Snowflake¶
--Create Table
CREATE OR REPLACE TABLE bfile_table
(
col1
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND ***/!!!
VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
--Insert Bfilename
INSERT INTO bfile_table
VALUES (PUBLIC.BFILENAME_UDF('mydirectory', 'myfile.png')
);
--Select
SELECT * FROM
bfile_table;
COL1 |
----------------------+
mydirectory\myfile.png|
UDF added to replace BFILENAME().
UDF Added
CREATE OR REPLACE FUNCTION PUBLIC.BFILENAME_UDF (DIRECTORYNAME STRING, FILENAME STRING)
RETURNS STRING
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
DIRECTORYNAME || '\\' || FILENAME
$$;
Known Issues¶
1. No access to the DBMS_LOB built-in package¶
Since LOB data types are not supported in Snowflake there is no equivalent for the DBMS_LOB functions and there are no implemented workarounds yet.
Related EWIs¶
SSC-EWI-OR0105: Additional work is needed for BFILE column usage. BUILD_STAGE_URL function is a recommended workaround.
NCLOB Data type¶
Description¶
A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. (Oracle SQL Language Reference NCLOB Data Type).
NCLOB Data Type is not supported in Snowflake. VARCHAR is used instead.
Sample Source Patterns¶
NCLOB in Create Table¶
Oracle¶
CREATE TABLE nclobtable ( nclob_column NCLOB, empty_column NCLOB );
INSERT INTO nclobtable VALUES ( 'THIS IS A TEST', EMPTY_CLOB() );
Snowflake¶
CREATE OR REPLACE TABLE nclobtable ( nclob_column VARCHAR,
empty_column VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO nclobtable
VALUES ( 'THIS IS A TEST', TO_VARCHAR(' - '));
Retrieving Data¶
Oracle¶
SELECT * FROM nclobtable;
NCLOB_COLUMN |EMPTY_COLUMN|
--------------+------------+
THIS IS A TEST| |
Snowflake¶
SELECT * FROM
nclobtable;
NCLOB_COLUMN |EMPTY_COLUMN|
--------------+------------+
THIS IS A TEST| - |
Known Issues¶
1. The difference in max length CLOB (Oracle) and VARCHAR (Snowflake)¶
An Oracle NCLOB column maximum size is (4 gigabytes - 1) * (database block size), but Snowflake VARCHAR is limited to 16MB.
2. Empty value with EMPTY_CLOB¶
Initializing a column using EMPTY_CLOB() will return an empty LOB locator. While after translation the column will return a string with ‘ - ‘.
3. No access to the DBMS_LOB built-in package¶
Since LOB data types are not supported in Snowflake there is not an equivalent for the DBMS_LOB functions and there are no implemented workarounds yet.
Related EWIs ¶
No related EWIs.
BLOB Data Type¶
Description¶
The BLOB data type stores unstructured binary large objects. BLOB objects can be thought of as bitstreams with no character set semantics. (Oracle SQL Language Reference BLOB Data Type).
BLOB Data Type is not supported in Snowflake. BINARY is used instead.
Sample Source Patterns¶
BLOB in Create Table¶
Oracle¶
CREATE TABLE blobtable( blob_column BLOB, empty_column BLOB );
INSERT INTO blobtable VALUES (NULL, EMPTY_BLOB());
Snowflake¶
CREATE OR REPLACE TABLE blobtable ( blob_column BINARY,
empty_column BINARY
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO blobtable
VALUES (NULL, TO_BINARY(' '));
Retrieving Data¶
Oracle¶
SELECT * FROM blobtable;
BLOB_COLUMN|EMPTY_COLUMN|
-----------+------------+
[NULL] |[BLOB] |
Snowflake¶
SELECT * FROM
blobtable;
BLOB_COLUMN|EMPTY_COLUMN|
-----------+------------+
NULL | |
Functional Example¶
This example is not a translation of SnowConvert, it is only used to show the functional equivalence between Oracle BLOB and Snowflake BINARY
We are using “utl_raw.cast_to_raw” and “DBMS_LOB.SUBSTR” functions. The conversion for these functions is currently not supported by SnowConvert.
Oracle¶
INSERT INTO blobtable VALUES(
utl_raw.cast_to_raw('hello world'), EMPTY_BLOB());
SELECT DBMS_LOB.SUBSTR(blob_column) AS result
FROM blobtable;
RESULT |
-----------+
[NULL] |
hello world|
Snowflake¶
INSERT INTO blobtable
VALUES(
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'utl_raw.cast_to_raw' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS cast_to_raw, TO_BINARY(' '));
SELECT
SUBSTR(blob_column, 1) AS result
FROM
blobtable;
RESULT |
-----------+
[NULL] |
hello world|
Known Issues¶
1. The difference in max length BLOB (Oracle) and BINARY (Snowflake)¶
An Oracle BLOB column’s maximum size is (4 gigabytes - 1) * (database block size), but Snowflake BINARY is limited to 8MB.
2. Empty value with EMPTY_BLOB¶
Initializing a column using EMPTY_BLOB() will return an empty LOB locator. While after translation the column will return a string with ‘ ‘.
3. No access to the DBMS_LOB built-in package¶
Since LOB data types are not supported in Snowflake there is no equivalent for the DBMS_LOB functions and there are no implemented workarounds yet.
Related EWIs¶
SSC-EWI-OR0076: Built In Package Not Supported.
CLOB Data Type¶
Description¶
A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. (Oracle SQL Language Reference CLOB Data Type).
CLOB Data Type is not supported in Snowflake. VARCHAR is used instead.
Sample Source Patterns¶
CLOB in Create Table¶
Oracle¶
CREATE TABLE clobtable ( clob_column CLOB, empty_column CLOB );
INSERT INTO clobtable VALUES ( 'THIS IS A TEST', EMPTY_CLOB() );
Snowflake¶
CREATE OR REPLACE TABLE clobtable ( clob_column VARCHAR,
empty_column VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
INSERT INTO clobtable
VALUES ( 'THIS IS A TEST', TO_VARCHAR(' - '));
Retrieving Data¶
Oracle¶
SELECT * FROM clobtable;
CLOB_COLUMN |EMPTY_COLUMN|
--------------+------------+
THIS IS A TEST| |
Snowflake¶
SELECT * FROM
clobtable;
CLOB_COLUMN |EMPTY_COLUMN|
--------------+------------+
THIS IS A TEST| - |
Known Issues¶
1. The difference in max length CLOB (Oracle) and VARCHAR (Snowflake)¶
An Oracle CLOB column maximum size is (4 gigabytes - 1) * (database block size), but Snowflake VARCHAR is limited to 16MB.
2. Empty value with EMPTY_CLOB¶
Initializing a column using EMPTY_CLOB() will return an empty LOB locator. While in Snowflake after translation the column will return a string with ‘ - ‘.
3. No access to the DBMS_LOB built-in package¶
Since LOB data types are not supported in Snowflake there is not an equivalent for the DBMS_LOB functions and there are no implemented workarounds yet.
Related EWIs ¶
No related EWIs.
ANSI Data Types¶
Description¶
SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle Database data type name. It converts the data type to the equivalent Oracle data type, records the Oracle data type as the name of the column data type, and stores the column data in the Oracle data type based on the conversions shown in the tables that follow. (Oracle Language Reference ANSI, DB2, and SQL/DS Data Types).
When creating a new table, Oracle and Snowflake handle some data types as synonyms and aliases and transform them into the default data type. As shown in the next table:
| ANSI | ORACLE | SNOWFLAKE |
|---|---|---|
| CHARACTER (n) | CHAR (n) | VARCHAR |
| CHAR (n) | CHAR (n) | VARCHAR |
| CHARACTER VARYING (n) | VARCHAR2 (n) | VARCHAR |
| CHAR VARYING (n) | VARCHAR2 (n) | VARCHAR |
| NATIONAL CHARACTER (n) | NCHAR (n) | VARCHAR* |
| NATIONAL CHAR (n) | NCHAR (n) | VARCHAR* |
| NCHAR (n) | NCHAR (n) | VARCHAR |
| NATIONAL CHARACTER VARYING (n) | NVARCHAR2 (n) | VARCHAR* |
| NATIONAL CHAR VARYING (n) | NVARCHAR2 (n) | VARCHAR* |
| NCHAR VARYING (n) | NVARCHAR2 (n) | NUMBER (p, s) |
| NUMERIC [(p, s)] | NUMBER (p, s) | NUMBER (p, s) |
| DECIMAL [(p, s)] | NUMBER (p, s) | NUMBER (38) |
| INTEGER | NUMBER (38) | NUMBER (38) |
| INT | NUMBER (38) | NUMBER (38) |
| SMALLINT | NUMBER (38) | NUMBER (38) |
| FLOAT | FLOAT (126) | DOUBLE |
| DOUBLE PRECISION | FLOAT (126) | DOUBLE |
| REAL | FLOAT (63) | DOUBLE |
VARCHAR*: Almost all the ANSI datatypes compile in Snowflake, but those marked with an asterisk, are manually converted to VARCHAR.
Known Issues¶
No issues were found.
Related EWIs¶
EWIs related to these data types are specified in the transformation of each data type.
URIFactory Package¶
Description¶
Oracle also provides the URIFactory package, which can create and return instances of the various subtypes of the URITypes. The package analyzes the URL string, identifies the type of URL (HTTP, DBURI, and so on), and creates an instance of the subtype. (Oracle SQL Language Reference URIFactory Package)
URIFactory contains the following subprograms:
GETURI¶
Oracle¶
SELECT SYS.URIFACTORY.GETURI('http://localhost/').GETURL() FROM dual;
|SYS.URIFACTORY.GETURI('HTTP://LOCALHOST/').GETURL()|
|---------------------------------------------------|
|http://localhost/ |
Snowflake¶
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'SYS.URIFACTORY.GETURI' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS GETURI.GETURL() FROM dual;
ESCAPEURI¶
Oracle¶
SELECT SYS.URIFACTORY.ESCAPEURI('http://www.<->') FROM dual;
|SYS.URIFACTORY.ESCAPEURI('HTTP://WWW.<->')|
|------------------------------------------|
|http://www.%3C-%3E |
Snowflake¶
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'SYS.URIFACTORY.ESCAPEURI' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS ESCAPEURI
FROM dual;
UNESCAPEURI¶
Oracle¶
SELECT SYS.URIFACTORY.UNESCAPEURI('http://www.%24-%26-%3C-%3E-%3F') FROM dual;
|SYS.URIFACTORY.UNESCAPEURI('HTTP://WWW.%24-%26-%3C-%3E-%3F')|
|------------------------------------------------------------|
|http://www.$-&-<->-? |
Snowflake¶
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0076 - TRANSLATION FOR BUILT-IN PACKAGE 'SYS.URIFACTORY.UNESCAPEURI' IS NOT CURRENTLY SUPPORTED. ***/!!!
'' AS UNESCAPEURI
FROM dual;
REGISTERURLHANDLER¶
Oracle¶
CREATE TABLE url_table (urlcol varchar2(80));
INSERT INTO url_table VALUES ('http://www.google.com/');
CREATE OR REPLACE TYPE SCURIType UNDER SYS.URIType (
OVERRIDING MEMBER FUNCTION getClob RETURN CLOB,
OVERRIDING MEMBER FUNCTION getBlob RETURN BLOB,
OVERRIDING MEMBER FUNCTION getExternalURL RETURN VARCHAR2,
OVERRIDING MEMBER FUNCTION getURI RETURN VARCHAR2,
STATIC FUNCTION createURI(url IN VARCHAR2) RETURN SCURIType);
/
CALL URIFACTORY.REGISTERURLHANDLER('sc://','HR','SCURITYPE');
INSERT INTO url_table VALUES ('SC://company1/company2=22/comp');
Snowflake¶
CREATE OR REPLACE TABLE url_table (urlcol VARCHAR(80))
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
INSERT INTO url_table
VALUES ('http://www.google.com/');
--!!!RESOLVE EWI!!! /*** SSC-EWI-OR0007 - CREATE TYPE SUBTYPE IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
--CREATE OR REPLACE TYPE SCURIType UNDER SYS.URIType (
-- OVERRIDING MEMBER FUNCTION getClob RETURN CLOB,
-- OVERRIDING MEMBER FUNCTION getBlob RETURN BLOB,
-- OVERRIDING MEMBER FUNCTION getExternalURL RETURN VARCHAR2,
-- OVERRIDING MEMBER FUNCTION getURI RETURN VARCHAR2,
-- STATIC FUNCTION createURI(url IN VARCHAR2) RETURN SCURIType)
;
CALL URIFACTORY.REGISTERURLHANDLER('sc://','HR','SCURITYPE');
INSERT INTO url_table
VALUES ('SC://company1/company2=22/comp');
UNREGISTERURLHANDLER¶
Oracle¶
CALL URIFACTORY.UNREGISTERURLHANDLER('sc://');
Snowflake¶
CALL URIFACTORY.UNREGISTERURLHANDLER('sc://');
Known Issues¶
1. Subprograms of URIFactory Package are not recognized
SnowConvert does not transform subprograms of built-in packages. The must of the functionality of URI types is not currently supported by Snowflake.
2. Missing EWIs for URIFactory Package
The output code should display an EWI indicating that some functionality is not supported by Snowflake. There is a work item to fix this issue.
Related EWIs¶
SSC-EWI-OR0007: Create Type Not Supported in Snowflake.
SSC-EWI-OR0076: Built In Package Not Supported.
XMLType¶
Description¶
This Oracle-supplied type can be used to store and query XML data in the database. XMLType has member functions you can use to access, extract, and query the XML data using XPath expressions. (Oracle SQL Language Reference XML Data Type)
Snowflake handles semi-structured data types (including XMLTYPE) using the VARIANT data type, for this reason, XMLTYPEs are to be migrated to VARIANT, and then usages of functions used to manipulate and query XML must be migrated to Snowflake’s counterparts. For more information on how to use XML in Snowflake, please refer to this post in the Snowflake forum and the TO_XML function documentation in Snowflake.
XMLTYPE
Sample Source Patterns¶
XMLType in Create Table¶
Oracle¶
CREATE TABLE xml_table(
xml_column XMLTYPE
);
Snowflake¶
CREATE OR REPLACE TABLE xml_table (
xml_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - XMLTYPE DATA TYPE CONVERTED TO VARIANT ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Insert data in the XML column¶
Oracle¶
INSERT INTO xml_table VALUES(
XMLType(
'<?xml version="1.0"?>
<note>
<to>SnowConvert</to>
<from>Oracle</from>
<heading>Greeting</heading>
<body>Hello there!</body>
</note>')
);
Snowflake¶
INSERT INTO xml_table
VALUES(
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0016 - FUNCTION RELATED WITH XML NOT SUPPORTED ***/!!!
XMLType(
'<?xml version="1.0"?>
<note>
<to>SnowConvert</to>
<from>Oracle</from>
<heading>Greeting</heading>
<body>Hello there!</body>
</note>')
);
Known Issues¶
1. XMLType manipulation and query functions are not recognized¶
The functions for manipulating and querying XML such as XMLTYPE() are not being recognized nor transformed by SnowConvert.
Related EWIs¶
SSC-EWI-0036: Data type converted to another data type.
SSC-EWI-OR0016: XML is not supported.
HTTPURIType¶
Some parts in the output code are omitted for clarity reasons.
Description¶
You can use HTTPURIType to store URLs to external Web pages or to files. Oracle accesses these files using HTTP (Hypertext Transfer Protocol). (Oracle SQL Language Reference URI Data Types)
HTTPURITYPE
Sample Source Patterns¶
HTTPURIType in create table¶
Oracle¶
CREATE TABLE httpuritype_table(
http_uritype_column HTTPURITYPE,
sys_http_uritype_column SYS.HTTPURITYPE
);
INSERT INTO httpuritype_table (http_uritype_column) VALUES(
HTTPURITYPE.createuri('http://localhost/')
);
INSERT INTO httpuritype_table (http_uritype_column) VALUES(
HTTPURITYPE.createuri('www.google.com')
);
Snowflake¶
CREATE OR REPLACE TABLE httpuritype_table (
http_uritype_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'HTTPURITYPE' USAGE CHANGED TO VARIANT ***/!!!,
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
sys_http_uritype_column SYS.HTTPURITYPE
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "" }}'
;
CREATE OR REPLACE VIEW PUBLIC.httpuritype_table_view
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "" }}'
AS
SELECT
http_uritype_column,
sys_http_uritype_column
FROM
httpuritype_table;
INSERT INTO httpuritype_table(http_uritype_column) VALUES(
HTTPURITYPE.createuri('http://localhost/') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'HTTPURITYPE.createuri' NODE ***/!!!
);
INSERT INTO httpuritype_table(http_uritype_column) VALUES(
HTTPURITYPE.createuri('www.google.com') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'HTTPURITYPE.createuri' NODE ***/!!!
);
Retrieving data from HTTPURIType column¶
Oracle¶
SELECT
ut.http_uritype_column.getUrl(),
ut.http_uritype_column.getExternalUrl()
FROM
httpuritype_table ut;
|UT.HTTP_URITYPE_COLUMN.GETURL()|UT.HTTP_URITYPE_COLUMN.GETEXTERNALURL()|
|-------------------------------|---------------------------------------|
|http://localhost/ |http://localhost/ |
|http://www.google.com |http://www.google.com |
Snowflake¶
SELECT
ut.http_uritype_column.getUrl() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ut.http_uritype_column.getUrl' NODE ***/!!!,
ut.http_uritype_column.getExternalUrl() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ut.http_uritype_column.getExternalUrl' NODE ***/!!!
FROM
httpuritype_table ut;
getUrl and getExternalUrl functions are not being transformed by the tool, but are necessary to display the data in Oracle, this transformation is going to be available in future releases.
Known Issues¶
1. HTTPURIType Data Type not recognized
HTTPURIType is parsed and converted as Custom Data Type by SnowConvert or as not supported type if it uses the prefix SYS, there is a work item to fix this issue
Related EWIs¶
SSC-EWI-0028: Type not supported.
SSC-EWI-0062: Custom type usage changed to variant.
SSC-EWI-0073: Pending functional equivalence review.
XDBURIType¶
Some parts in the output code are omitted for clarity reasons.
Description¶
You can use XDBURIType to expose documents in the XML database hierarchy as URIs that can be embedded in any URIType column in a table. The XDBURIType consists of a URL, which comprises the hierarchical name of the XML document to which it refers and an optional fragment representing the XPath syntax. (Oracle SQL Language Reference URI Data Types)
XDBURITYPE
Sample Source Patterns¶
XDBURIType in create table¶
Oracle¶
CREATE TABLE xdburitype_table(
xdb_uritype_column XDBURITYPE,
sys_xdb_uritype_column SYS.XDBURITYPE
);
INSERT INTO xdburitype_table (xdb_uritype_column) VALUES(
xdburitype('/home/OE/employees/emp_selby.xml')
);
Snowflake¶
CREATE OR REPLACE TABLE xdburitype_table (
xdb_uritype_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'XDBURITYPE' USAGE CHANGED TO VARIANT ***/!!!,
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
sys_xdb_uritype_column SYS.XDBURITYPE
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
CREATE OR REPLACE VIEW PUBLIC.xdburitype_table_view
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "" }}'
AS
SELECT
xdb_uritype_column,
sys_xdb_uritype_column
FROM
xdburitype_table;
INSERT INTO xdburitype_table(xdb_uritype_column) VALUES(
xdburitype('/home/OE/employees/emp_selby.xml') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'xdburitype' NODE ***/!!!
);
Retrieving data from XDBURIType column¶
Oracle¶
SELECT ut.xdb_uritype_column.getclob() FROM xdburitype_table ut;
|UT.XDB_URITYPE_COLUMN.GETCLOB()|
|-------------------------------|
|<emp_name>selby</emp_name> |
This result query has XML syntax, this is how it is displayed:
<emp_name>selby</emp_name>
Snowflake¶
SELECT ut.xdb_uritype_column.getclob() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ut.xdb_uritype_column.getclob' NODE ***/!!! FROM
xdburitype_table ut;
getclob function is not being transformed by the tool, but is necessary to display the data in Oracle, this transformation is going to be available in future releases.
Known Issues¶
1. XDBURIType Data Type not recognized
XDBURIType is parsed and converted as Custom Data Type by SnowConvert or as not supported type if it uses the prefix SYS, there is a work item to fix this issue
Related EWIs¶
SSC-EWI-0028: Type not supported.
SSC-EWI-0062: Custom type usage changed to variant
SSC-EWI-0073: Pending functional equivalence review
DBURIType¶
Some parts in the output code are omitted for clarity reasons.
Description¶
DBURIType can be used to store DBURIRef values, which reference data inside the database. Storing DBURIRef values lets you reference data stored inside or outside the database and access the data consistently. (Oracle SQL Language Reference URI Data Types)
DBURIType
Sample Source Patterns¶
Check this section to set up the sample database.
DBURIType in create table¶
Oracle¶
CREATE TABLE dburitype_table(
db_uritype_column DBURITYPE,
sys_db_uritype_column SYS.DBURITYPE
);
INSERT INTO dburitype_table (db_uritype_column) VALUES (
dburitype.createUri('/HR/EMPLOYEES/ROW[EMPLOYEE_ID=205]/FIRST_NAME ')
);
Snowflake¶
CREATE OR REPLACE TABLE dburitype_table (
db_uritype_column VARIANT !!!RESOLVE EWI!!! /*** SSC-EWI-0062 - CUSTOM TYPE 'DBURITYPE' USAGE CHANGED TO VARIANT ***/!!!,
!!!RESOLVE EWI!!! /*** SSC-EWI-0028 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/!!!
sys_db_uritype_column SYS.DBURITYPE
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
CREATE OR REPLACE VIEW PUBLIC.dburitype_table_view
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "oracle", "convertedOn": "" }}'
AS
SELECT
db_uritype_column,
sys_db_uritype_column
FROM
dburitype_table;
INSERT INTO dburitype_table(db_uritype_column) VALUES (
dburitype.createUri('/HR/EMPLOYEES/ROW[EMPLOYEE_ID=205]/FIRST_NAME ') !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'dburitype.createUri' NODE ***/!!!
);
Retrieving data from DBURIType column¶
Oracle¶
SELECT dt.db_uritype_column.getclob() FROM dburitype_table dt;
|DT.DB_URITYPE_COLUMN.GETCLOB() |
|--------------------------------------------------------|
|<?xml version="1.0"?>¶ <FIRST_NAME>Shelley</FIRST_NAME>¶|
This result query has XML syntax, this is how it is displayed:
<?xml version="1.0"?>
<FIRST_NAME>Shelley</FIRST_NAME>
Snowflake¶
SELECT dt.db_uritype_column.getclob() !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'dt.db_uritype_column.getclob' NODE ***/!!! FROM
dburitype_table dt;
getclob function is not being transformed by the tool, but is necessary to display the data in Oracle, this transformation is going to be available in future releases.
Known Issues¶
1. DBURIType Data Type not recognized
DBURIType is parsed and converted as Custom Data Type by SnowConvert or as not supported type if it uses the prefix SYS, there is a work item to fix this issue
Related EWIs¶
SSC-EWI-0028: Type not supported.
SSC-EWI-0062: Custom type usage changed to variant
SSC-EWI-0073: Pending functional equivalence review
User-Defined Types¶
Description¶
User-defined data types use Oracle built-in data types and other user-defined data types as the building blocks of object types that model the structure and behavior of data in applications. The sections that follow describe the various categories of user-defined types. (Oracle SQL Language Reference User-defined Data Types)
Warning
Snowflake does not have any support for User-defined Types. This page is meant to be a summary of Oracle’s features. For the current status of User-defined Types in the SnowConvert tool please refer to the Create Type Statement Page and its subpages.
Object Types¶
Note
SnowConvert offers partial translation for Object Types, for more information on this, please refer to the next section:
REF Data Types¶
Note
Ref Data Types are not recognized by SnowConvert, and are instead shown as unrecognized “User-defined Functions”. For more information about them, please read the REF Data Types subpage.
An object identifier (represented by the keyword OID) uniquely identifies an object and enables you to reference the object from other objects or from relational tables. A data type category called REF represents such references. A REF data type is a container for an object identifier. REF values are pointers to objects. (Oracle SQL Language Reference REF Data Types)
Varrays¶
Warning
SnowConvert only recognizes these elements but does not offer any translation for them, for more information on this, please refer to the next section:
Nested Tables¶
Warning
SnowConvert only recognizes these elements but does not offer any translation for them since there are no known workarounds for them, for more information on this, please refer to the next section:
Known Issues¶
1. DML usages for Object Types are not being transformed¶
As of now, only DDL definitions that use User-Defined Types are being transformed into Variant. This means that any Inserts, Updates or Deletes using User-defined Types are not being transformed and need to be manually transformed. There is no EWI for this but there is a work item to add this corresponding EWI.
2. Nested Table types are not being transformed¶
There is no known workaround for implementing Nested Tables, for this reason SnowConvert only offers recognition of these elements.
3. Array types are not being transformed¶
For now SnowConvert only recognizes these elements. A known workaround exists and there is a work item to implement them.
Related EWIs¶
No related EWIs.
REF Data Types¶
Note
Some parts in the output code are omitted for clarity reasons.
Description¶
An object identifier (represented by the keyword OID) uniquely identifies an object and enables you to reference the object from other objects or relational tables. A data type category called REF represents such references. A REF data type is a container for an object identifier. REF values are pointers to objects. (Oracle SQL Language Reference REF Data Types)
REF Data types are not supported in Snowflake, and there is no current workaround to implement a similar component.
As of now, they are currently being recognized as user-defined functions and “DANGLING” clauses are not being recognized. Finally, the OID clause in view is being removed, as there is no workaround for them.
CREATE VIEW generic_view AS
SELECT REF(type) AS ref_col, MAKE_REF(type, identifier_column) AS make_ref_col
FROM generic_table;
SELECT v.ref_col, v.make_ref_col
FROM generic_view v
WHERE v.ref_col IS NOT DANGLING AND v.make_ref_col IS NOT DANGLING
Sample Source Patterns¶
Types and Tables for References¶
Please consider the following types, tables, inserts and view. They will be used for the next pattern section.
Oracle
CREATE TYPE email_typ_demo AS OBJECT
( email_id INTEGER
, email VARCHAR2(30)
);
CREATE TYPE customer_typ_demo AS OBJECT
( customer_id INTEGER
, cust_first_name VARCHAR2(20)
, cust_last_name VARCHAR2(20)
, email_id INTEGER
) ;
CREATE TABLE email_table_demo OF email_typ_demo;
CREATE TABLE customer_table_demo OF customer_typ_demo;
INSERT INTO customer_table_demo VALUES
(customer_typ_demo(1, 'First Name 1', 'Last Name 1', 1));
INSERT INTO customer_table_demo VALUES
(customer_typ_demo(2, 'First Name 2', 'Last Name 2', 2));
INSERT INTO email_table_demo VALUES
(email_typ_demo(1, 'abc@def.com'));
CREATE VIEW email_object_view OF email_typ_demo WITH OBJECT IDENTIFIER (email_id) AS
SELECT * FROM email_table_demo;
Selects and Views using REFs¶
Oracle
CREATE VIEW email_object_view OF email_typ_demo WITH OBJECT IDENTIFIER (email_id) AS
SELECT * FROM email_table_demo;
CREATE VIEW customer_view AS
SELECT REF(ctb) AS customer_reference
, MAKE_REF(email_object_view, ctb.email_id) AS email_ref
FROM customer_table_demo ctb;
SELECT c.customer_reference.cust_first_name, c.email_ref.email
FROM customer_view c;
SELECT c.customer_reference.cust_first_name, c.email_ref.email
FROM customer_view c
WHERE c.email_ref IS NOT DANGLING;
CUSTOMER_REFERENCE.CUST_FIRST_NAME|EMAIL_REF.EMAIL|
----------------------------------+---------------+
First Name 1 |abc@def.com |
First Name 2 | |
CUSTOMER_REFERENCE.CUST_FIRST_NAME|EMAIL_REF.EMAIL|
----------------------------------+---------------+
First Name 1 |abc@def.com |
Snowflake
CREATE OR REPLACE VIEW email_object_view
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
AS
--** SSC-FDM-0001 - VIEWS SELECTING ALL COLUMNS FROM A SINGLE TABLE ARE NOT REQUIRED IN SNOWFLAKE AND MAY IMPACT PERFORMANCE. **
SELECT * FROM
email_table_demo;
CREATE OR REPLACE VIEW customer_view
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
AS
SELECT REF(ctb) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'REF' NODE ***/!!! AS customer_reference
, MAKE_REF(email_object_view, ctb.email_id) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'MAKE_REF' NODE ***/!!! AS email_ref
FROM
customer_table_demo ctb;
SELECT c.customer_reference.cust_first_name, c.email_ref.email
FROM
customer_view c;
SELECT c.customer_reference.cust_first_name, c.email_ref.email
FROM
customer_view c
WHERE c.email_ref;
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '14' COLUMN '19' OF THE SOURCE CODE STARTING AT 'IS'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS ';' ON LINE '10' COLUMN '21'. FAILED TOKEN WAS 'IS' ON LINE '14' COLUMN '19'. CODE '94'. **
-- IS NOT DANGLING
Known Issues¶
1. REF and MAKE_REF are not being recognized
Instead they are currently being marked as user-defined functions.
2. DANGLING clause is not being recognized
DANGLING clauses are causing parsing errors when running SnowConvert.
Related EWIs¶
SSC-EWI-0001: Unrecognized token on the line of the source code.
SSC-EWI-0073: Pending Functional Equivalence Review.
SSC-FDM-0001: Views selecting all columns from a single table are not required in Snowflake.