Description
This type contains an instance of a given type, with data, plus a description of the type. ANYDATA
can be used as a table column data type and lets you store heterogeneous values in a single column. The values can be of SQL built-in types as well as user-defined types. (Oracle SQL Language Reference ANYDATA Data Type ).
The ANYDATA
data type is not supported in Snowflake.
Copy { SYS.ANYDATA | ANYDATA }
Sample Source Patterns
Create Table with ANYDATA
Oracle
Query
Copy CREATE TABLE anydatatable
(
col1 NUMBER,
col2 ANYDATA,
col3 SYS.ANYDATA
);
Snowflake
Query
Copy CREATE OR REPLACE TABLE anydatatable
(
col1 NUMBER( 38 , 18 ) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ ,
col2 VARIANT,
col3 VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
Inserting data into ANYDATA column
Oracle
Query
Copy INSERT INTO anydatatable VALUES (
555 ,
ANYDATA.ConvertVarchar( 'Another Test Text' )
);
Snowflake
Query
Copy INSERT INTO anydatatable
VALUES (
555 ,
ANYDATA.ConvertVarchar( 'Another Test Text' ) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertVarchar' NODE ***/ !!!
);
Functional Example
This example is not a translation of SnowConvert, it is only used to show the functional equivalence between Oracle ANYDATA
and Snowflake VARIANT
We are using the ANYDATA
built-in package. The conversion for this package is currently not supported by SnowConvert.
Oracle
Query Result
Copy --Create Table
CREATE TABLE anydatatable_example
(
col1 ANYDATA,
col2 ANYDATA,
col3 ANYDATA,
col4 ANYDATA,
col5 ANYDATA
);
--Insert data
INSERT INTO anydatatable_example VALUES (
ANYDATA.ConvertNumber( 123 ),
ANYDATA.ConvertVarchar( 'Test Text' ),
ANYDATA.ConvertBFloat( 3 .14f),
ANYDATA.ConvertDate(CURRENT_DATE),
ANYDATA.ConvertTimestamp(CURRENT_TIMESTAMP)
);
--Retrieve information
SELECT
ANYDATA.AccessNumber(col1) AS col1,
ANYDATA.AccessVarchar(col2) AS col2,
ANYDATA.AccessBFloat(col3) AS col3,
ANYDATA.AccessDate(col4) AS col4,
ANYDATA.AccessTimestamp(col5) AS col5
FROM anydatatable_example;
Copy COL1|COL2 |COL3|COL4 |COL5 |
----+---------+----+-----------------------+-----------------------+
123|Test Text|3.14|2021-12-05 18:24:59.000|2021-12-05 18:24:59.100|
Snowflake
Query Result
Copy --Create Table
CREATE OR REPLACE TABLE anydatatable_example
(
col1 VARIANT,
col2 VARIANT,
col3 VARIANT,
col4 VARIANT,
col5 VARIANT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;
--Insert data
INSERT INTO anydatatable_example
VALUES (
ANYDATA.ConvertNumber( 123 ) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertNumber' NODE ***/ !!!,
ANYDATA.ConvertVarchar( 'Test Text' ) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertVarchar' NODE ***/ !!!,
ANYDATA.ConvertBFloat( 3 . 14 ) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertBFloat' NODE ***/ !!!,
ANYDATA.ConvertDate(CURRENT_DATE()) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertDate' NODE ***/ !!!,
ANYDATA.ConvertTimestamp(CURRENT_TIMESTAMP()) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.ConvertTimestamp' NODE ***/ !!!
);
--Retrieve information
SELECT
ANYDATA.AccessNumber(col1) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.AccessNumber' NODE ***/ !!! AS col1,
ANYDATA.AccessVarchar(col2) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.AccessVarchar' NODE ***/ !!! AS col2,
ANYDATA.AccessBFloat(col3) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.AccessBFloat' NODE ***/ !!! AS col3,
ANYDATA.AccessDate(col4) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.AccessDate' NODE ***/ !!! AS col4,
ANYDATA.AccessTimestamp(col5) !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'ANYDATA.AccessTimestamp' NODE ***/ !!! AS col5
FROM
anydatatable_example;
Copy COL1|COL2 |COL3|COL4 |COL5 |
----+-----------+----+------------+-------------------------------+
123 |"Test Text"|3.14|"2021-12-05"|"2021-12-05 18:24:43.326 -0800"|
Known Issues
1. No access to the ANYDATA built-in package
Most operations with ANYDATA
columns require to use the ANYDATA
built-in package, transformation for Oracle built-in packages is not supported by SnowConvert yet.
Related EWIs
Last updated 10 months ago