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

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

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.anydatatable (
col1 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
col2 VARIANT /*** MSC-WARNING - MSCEWI1036 -  ANYDATA DATA TYPE CONVERTED TO VARIANT ***/,
col3 VARIANT /*** MSC-WARNING - MSCEWI1036 -  SYS.ANYDATA DATA TYPE CONVERTED TO VARIANT ***/);

Inserting data into ANYDATA column

Oracle

Query

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

Snowflake

Query

 INSERT INTO PUBLIC.anydatatable VALUES(
 	555, /*** MSC-WARNING - MSCEWI1049 - ANYDATA.ConvertVarchar FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
 PUBLIC.ANYDATA.ConvertVarchar_UDF('ANYDATA.ConvertVarchar(\'Another Test Text\')')
 );

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

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

--Create Table
CREATE OR REPLACE TABLE anydatatable_example (
  col1 VARIANT,
  col2 VARIANT,
  col3 VARIANT,
  col4 VARIANT,
  col5 VARIANT 
);

--Insert data
INSERT INTO anydatatable_example
SELECT
	to_variant(123),
	to_variant('Test Text'),
	to_variant(3.14),
	to_variant(CURRENT_DATE),
	to_variant(CURRENT_TIMESTAMP);
	

SELECT
	col1,
	col2,
	col3,
	col4,
	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. MSCEWI1028: Type is not supported.

2. MSCEWI1049: Not recognized function.

3. MSCEWI1036: Data type converted to another data type