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. ***/,
	--** SSC-FDM-0026 - TYPE NOT SUPPORTED BY SNOWFLAKE **
		col2 ANYDATASET,
	--** SSC-FDM-0026 - 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
    /*** SSC-FDM-0026 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/
    anytype_example    ANYTYPE;
    /*** SSC-FDM-0026 - TYPE NOT SUPPORTED BY SNOWFLAKE ***/
    anydataset_example ANYDATASET;
BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR ANYDATASET.BEGINCREATE. CHECK IF THE NAME IS INVALID OR DUPLICATED. ***/!!!
    CALL
    ANYDATASET.BEGINCREATE(
    --** SSC-FDM-OR0041 - TRANSLATION FOR BUILT-IN PACKAGE 'DBMS_TYPES.TYPECODE_VARCHAR2' IS NOT CURRENTLY SUPPORTED. **
    '' AS TYPECODE_VARCHAR2, :anytype_example, :anydataset_example);
    !!!RESOLVE EWI!!! /*** SSC-EWI-0039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR anydataset_example.ADDINSTANCE. CHECK IF THE NAME IS INVALID OR DUPLICATED. ***/!!!
    CALL

    anydataset_example.ADDINSTANCE();
    !!!RESOLVE EWI!!! /*** SSC-EWI-0039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR anydataset_example.SETVARCHAR2. CHECK IF THE NAME IS INVALID OR DUPLICATED. ***/!!!
    CALL
    anydataset_example.SETVARCHAR2('First element');
    !!!RESOLVE EWI!!! /*** SSC-EWI-0039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR anydataset_example.ADDINSTANCE. CHECK IF THE NAME IS INVALID OR DUPLICATED. ***/!!!
    CALL

    anydataset_example.ADDINSTANCE();
    !!!RESOLVE EWI!!! /*** SSC-EWI-0039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR anydataset_example.SETVARCHAR2. CHECK IF THE NAME IS INVALID OR DUPLICATED. ***/!!!
    CALL
    anydataset_example.SETVARCHAR2('Second element');
    !!!RESOLVE EWI!!! /*** SSC-EWI-0039 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR ANYDATASET.ENDCREATE. CHECK IF THE NAME IS INVALID OR DUPLICATED. ***/!!!
    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-FDM-OR0041: Built In Package Not Supported.

2. SSC-EWI-0039: Sematic information could not be loaded.

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

4. SSC-FDM-0026: Type not supported by Snowflake.