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

Query

IN -> Oracle_01.sql

CREATE TABLE anydatatable
(
    col1 NUMBER,
    col2 ANYDATA,
    col3 SYS.ANYDATA
);

Snowflake

Query

OUT -> Oracle_01.sql

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

Query

IN -> Oracle_02.sql

INSERT INTO anydatatable VALUES(
	555,
	ANYDATA.ConvertVarchar('Another Test Text')
);

Snowflake

Query

OUT -> Oracle_02.sql

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

Query

IN -> Oracle_03.sql

--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

Query

OUT -> Oracle_03.sql

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

Related EWIs

1. SSC-FDM-0006: Number type column may not behave similarly in Snowflake.

2. SSC-EWI-0073: Pending Functional Equivalence Review.