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

Query

CREATE TABLE anytypetable 
(
	col1 NUMBER,
	col2 ANYTYPE,
	col3 SYS.ANYTYPE
);

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.anytypetable (
col1 NUMBER (38,18) /*** MSC-WARNING - MSCEWI1066 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE ***/,
-- ** MSC-ERROR - MSCEWI1028 - TYPE NOT SUPPORTED **
--col2 ANYTYPE
            ,
-- ** MSC-ERROR - MSCEWI1028 - TYPE NOT SUPPORTED **
--col3 SYS.ANYTYPE
                );

Inserting data into ANYTYPE column

Oracle

Query

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

Query

-- ** MSC-WARNING - MSCEWI1056 - CUSTOM TYPES ARE NOT SUPPORTED IN SNOWFLAKE BUT REFERENCES TO THIS CUSTOM TYPE WERE CHANGED TO A VARIANT **
----Create Custom Type
--CREATE OR REPLACE TYPE example_type AS OBJECT (id NUMBER (38,18), name VARCHAR(20))
                                                                                   ;

--Insert
INSERT INTO PUBLIC.anytypetable VALUES(
    123, /*** MSC-WARNING - MSCEWI1049 - GETANYTYPEFROMPERSISTENT FUNCTION WAS NOT RECOGNIZED BY SNOWCONVERT ***/
 PUBLIC.GETANYTYPEFROMPERSISTENT_UDF('GETANYTYPEFROMPERSISTENT (\'HR\', \'EXAMPLE_TYPE\')')
);

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

1. MSCEWI1028: Type is not supported.

2. MSCEWI1049: Not recognized function.