SnowConvert AI - Oracle - Any Types¶

Description¶

The Any types provide highly flexible modeling of procedure parameters and table columns where the actual type is not known. These data types let you dynamically encapsulate and access type descriptions, data instances, and sets of data instances of any other SQL type. (Oracle SQL Language Reference ANYTYPES Data Types)

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¶

Warning

This example is not a translation of SnowConvert AI, it is only used to show the functional equivalence between Oracle ANYDATA and Snowflake VARIANT

Warning

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

ANYDATASET¶

Note

Some parts in the output code are omitted for clarity reasons.

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.

1. 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 AI 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
!!!RESOLVE EWI!!! /*** SSC-EWI-0056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO VARIANT ***/!!!
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 AI 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.