ANYDATASET

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

Query

IN -> Oracle_01.sql

CREATE TABLE anydatasettable
(
	col1 NUMBER,
	col2 ANYDATASET,
	col3 SYS.ANYDATASET
);

Snowflake

Query

OUT -> Oracle_01.sql

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

Query

IN -> Oracle_02.sql

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

Query

OUT -> Oracle_02.sql

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

Related EWIs

1. SSC-EWI-OR0076: Built In Package Not Supported.

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

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