RAW Data Type - Oracle to SQL Server Migration

In Oracle, RAW data type allows you to store variable-length binary data up to 2000 bytes. You can convert Oracle RAW(n) to VARBINARY(n) in SQL Server.

Oracle Example:

   -- Define a table with RAW column
   CREATE TABLE rawdata
   (
      c1 RAW(11)
   ); 
 
   -- Insert one byte (value 1)
   INSERT INTO rawdata VALUES ('1');

SQL Server Example:

   -- Define a table with VARBINARY column
   CREATE TABLE rawdata
   (
      c1 VARBINARY(11)
   ); 
 
   -- Insert one byte (value 1)
   INSERT INTO rawdata VALUES (0x1);

RAW Data Type Migration Overview

Oracle RAW(n) data type to SQL Server conversion summary:

Oracle SQL Server
Syntax RAW(n) VARBINARY[(n)]
Parameter n is the maximum number of bytes
Must be specified Optional, default is 1
Range 1 ⇐ n ⇐ 2000 1 ⇐ n ⇐ 8000 Use VARBINARY(max) for data up to 2 GB
Insert Syntax Hex number as a string literal Hex or integer number,
string with CAST (different data inserted )
Alternatives BLOB LONG RAW (deprecated) BINARY (fixed length) IMAGE (deprecated)

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

RAW Data Type Migration Specifics

In Oracle, you can use a string literal containing a hex value to insert data into a RAW column:

Oracle:

   -- Define a table with RAW column
   CREATE TABLE rawdata
   (
      c1 RAW(11)
   ); 
 
   -- Insert hex value AB (1 byte)
   INSERT INTO rawdata VALUES ('AB');
 
  -- Inserts hex value ABCD (2 bytes)
  INSERT INTO rawdata VALUES ('ABCD');

Note that Oracle does not convert a string to hex representation, the string literal must already contain hex values. Also Oracle does not allow inserting number values.

Oracle:

    -- AZ is not valid hex value
    INSERT INTO rawdata VALUES ('AZ');
    -- ERROR at line 1:
    -- ORA-01465: invalid hex number
 
    -- Numbers are not allowed
    INSERT INTO rawdata VALUES (100);
    -- ERROR at line 1:
    -- ORA-00932: inconsistent datatypes: expected BINARY got NUMBER

In Oracle, you can use RAWHOTEXT and DUMP functions, to output the context of RAW data:

Oracle:

   -- Output the content of RAW data
   SELECT RAWTOHEX(c1), DUMP(c1) FROM rawdata;

Output:

RAWHOHEX DUMP
AB Typ=23 Len=1: 171
ABCD Typ=23 Len=2: 171, 205

The hex value AB corresponds to the decimal value 171, and the hex value CD to decimal value 205.

Value in INSERT in Oracle Hex Value Inserted Length in Bytes
'AB' AB 1
'ABCD' AB CD 2

Using VARBINARY(n) in SQL Server

You can replace RAW(n) with VARBINARY(n) in CREATE TABLE statement in SQL Server:

SQL Server:

   -- Define a table with RAW column
   CREATE TABLE rawdata
   (
      c1 VARBINARY(11)
   );

But INSERT syntax to insert data into Oracle RAW and SQL Server columns is completely different

In SQL Server, you can use CAST function to insert a value from a string literal into VARBINARY column, but the inserted value is not the same as if you insert a string literal into RAW column in Oracle.

SQL Server:

    -- Insert without cast fails
    INSERT INTO rawdata VALUES ('AB');
    -- Msg 257, Level 16, State 3, Line 1
    -- Implicit conversion from data type varchar to varbinary is not allowed. 
 
    INSERT INTO rawdata VALUES (CAST('AB' AS VARBINARY));
    -- 1 row(s) affected

Although you can successfully cast 'AB' as VARBINARY, SQL Server treats 'AB' as a 2-byte string, and converts it to 0x4142 hex number before insert.

Table content after inserting 'AB':

Oracle SQL Server
0xAB 0x4142

To insert a hex value AB to a VARBINARY column in SQL Server, you can use hex number, not a string literal:

SQL Server:

    -- Insert hex value AB (1 byte)
    INSERT INTO rawdata VALUES (0xAB);

Table content after inserting 'AB' in Oracle and 0xAB in SQL Server:

Oracle SQL Server
0xAB 0xAB

Inserting Numbers

The following statement inserts a single byte with value 1 into a RAW column in Oracle:

Oracle:

   -- Insert one byte with value 1
   INSERT INTO rawdata VALUES ('1');
 
   -- Dump the content
   SELECT DUMP(c1) FROM rawdata WHERE c1 = '01';
   -- Typ=23 Len=1: 1

In SQL Server, you have to specify 0x1, not 1 or '1' to insert the same value:

SQL Server:

    INSERT INTO rawdata VALUES ('1');
    -- Msg 257, Level 16, State 3, Line 1
    -- Implicit conversion from data type varchar to varbinary is not allowed.
 
    -- Inserts 0x31 (ASCII code of 1)
    INSERT INTO rawdata VALUES (CAST('1' AS VARBINARY));
 
    -- Inserts 0x00000001 - 4 bytes (!)
    INSERT INTO rawdata VALUES (1);
 
    -- Inserts 0x01 - 1 byte with value 1
    INSERT INTO rawdata VALUES (0x1);

Let's now get the table content and data size for all insert statements:

SQL Server:

   -- Get column data and length in bytes
   SELECT c1, DATALENGTH(c1) FROM rawdata;

The result:

Value in INSERT Inserted value for c1 c1 Data length in bytes
CAST('AB' AS VARBINARY) 0x4142 2
0xAB 0xAB 1
CAST('1 AS VARBINARY) 0x31 1
1 0x00000001 4
0x01 0x01 1

Resources

Oracle 11g R2 SQL Language Reference

Microsoft SQL Server 2012 - Books Online

Author

Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012

You could leave a comment if you were logged in.