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

IN -> Oracle_01.sql

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

Snowflake

Query

OUT -> Oracle_01.sql

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

Query

IN -> Oracle_02.sql

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

OUT -> Oracle_02.sql

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

Related EWIs

1. SSC-EWI-0056: Create Type Not Supported.

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

3. SSC-EWI-0028: Type not supported in Snowflake.

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