Migrating Oracle ANYDATA to SQL Server

  • [アーティクル]

By Welly Lee, Pinaki Bag, and Jayakumar Tanneru.

Oracle has an object data type called anydata. This data type supports wide range of data types. For example when creating a table with a column defined as anydata type, the column can store many types of data from string to numeric . SSMA does not support migration of anydata and when migrating Oracle database containing the type, SSMA raise a migration error O2SS0005: Source datatype not recognized.

SQL Server has a similar data type called sql_variant. sql_variant provides simpler data management and provide additional capabilities:

  1. Oracle anydata is an object type that includes static and member procedures and functions. Using anydata type requires the use of convert function to store and an utility function to retrieve the value. sql_variant stores data value directly and values can be retrieved without explicit conversion.

    Oracle

    SQL Server

    CREATE TABLE tbl1 (col1 anydata);

    CREATE TABLE tbl1 (col1 sql_variant)

    INSERT INTO tbl1 (col1) VALUES (sys.anydata.convertnumber(123.4));

    INSERT INTO tbl1 (col1) VALUES (123.4)

    INSERT INTO tbl1 (col1) VALUES (sys.anydata.convertvarchar2('abc'));

    INSERT INTO tbl1 (col1) VALUES ('abc')

    INSERT INTO tbl1 (col1) VALUES (sys.anydata.convertdate(sysdate));

    INSERT INTO tbl1 (col1) VALUES (getdate())
    SELECT * FROM tbl1; COL1() ------------------------------- ANYDATA() ANYDATA() ANYDATA()  /* anydata values can be retrieving by using function such as described in this article */ SELECT getData(col1) FROM tbl1; GETDATA(COL1) --------------------------------- 123.4 abc 21-JUN-11 SELECT * FROM tbl1 Col1 -------------------- 123.4 abc 2011-06-21 00:00:00.001

    /* note that a sql_variant data type must first be cast to its base data type value before participating in operations such as addition and subtraction. For example: */

    DECLARE @var sql_variant SET @var = 2 SELECT CONVERT(int,@var) - 1

  2. Index cannot be created on the column with anydata type. sql_variant column can be included in the index (as long as the value does not exceed 900 bytes).

    -- Sample SQL Server statement:
    CREATE INDEX idx_test ON tbl1 (col1)

  3. sql_variant can be assigned a default value. This data type can also have NULL as its underlying value, but the NULL values will not have an associated base type.

    -- Sample SQL Server statements:
    CREATE TABLE tbl2 (col1 int, col2 sql_variant DEFAULT 'some value')
    CREATE TABLE tbl3 (col1 int, col2 sql_variant DEFAULT NULL)

For many cases, sql_variant can be used to replace anydata type when migrating Oracle to SQL Server. However, note the following limitation:

  1. sql_variant supports smaller size and fewer data types. sql_variant can have a maximum length of 8016 bytes. This includes both the base type information and the base type value. The maximum length of the actual base type value is 8,000 bytes. sql_variant also have limitation for supporting the following types:
    Oracle Data Type Comparable SQL Server Data Type
    raw varbinary(max)
    blob varbinary(max)
    cblob varchar(max)
    bfile FILESTREAM storage
    user defined type user defined type
    timestamp timestamp/datetime2
    interval  
    spatial (geography/geometry) spatial (geography/geometry)
    xmltype xml
  2. ODBC does not fully support sql_variant. Therefore, queries of sql_variant columns are returned as binary data when you use Microsoft OLE DB Provider for ODBC (MSDASQL).

If you are unable to use sql_variant due to the technical limitation above, you may consider to migrate the anydata as separate columns based on its individual types (SQL Server 2008 supports Sparse Column to optimize storing data across multiple columns with many NULL values). Alternatively, you can also consider to convert anydata to xml type (note the limitations of xml data type)

-- Sample SQL Server Statements:
CREATE TABLE tbl1 (col1 xml)
INSERT INTO tbl1 VALUES ('<root><data>123.4</data><datatype>decimal(13,5)</datatype></root>')
INSERT INTO tbl1 VALUES ('<root><data>abc</data><datatype>varchar(max)</datatype></root>')
INSERT INTO tbl1 VALUES ('<root><data>'+cast(getdate() as varchar(max))+'</data><datatype>datetime</datatype></root>')

SELECT col1.value('(/root/data)[1]','varchar(max)') from tbl1  -- returning data values
SELECT col1.value('(/root/datatype)[1]','varchar(max)') from tbl1  -- returning data types

REFERENCE

SQL Server 2008R2 Books Online : sql_variant