ANYDATASET

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

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

Snowflake

Query

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

Inserting data into ANYDATASET column

Oracle

Query

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

--DECLARE
--    anytype_example    ANYTYPE
-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 118 OF THE SOURCE CODE. **
--    anydataset_example ANYDATASET
-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 119 OF THE SOURCE CODE. **
--BEGIN
--    ANYDATASET.BEGINCREATE(DBMS_TYPES.TYPECODE_VARCHAR2, anytype_example, anydataset_example)

-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 122 OF THE SOURCE CODE. **
--    anydataset_example.ADDINSTANCE
-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 123 OF THE SOURCE CODE. **
--    anydataset_example.SETVARCHAR2('First element')

-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 125 OF THE SOURCE CODE. **
--    anydataset_example.ADDINSTANCE
-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 126 OF THE SOURCE CODE. **
--    anydataset_example.SETVARCHAR2('Second element')

-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 128 OF THE SOURCE CODE. **
--    ANYDATASET.ENDCREATE(anydataset_example)

    INSERT INTO PUBLIC.anydatasettable VALUES (123, anydataset_example);
-- ** MSC-ERROR - MSCEWI1001 - UNRECOGNIZED TOKEN ON LINE 131 OF THE SOURCE CODE. **
--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. MSCEWI1001: Unrecognized token on the line of the source code.

2. MSCEWI1028: Type is not supported.

3. MSCEWI1049: Not recognized function.