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);

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

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:

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

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':

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:

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:

• Oracle to SQL Server Migration

Oracle 11g R2 SQL Language Reference

• Data Types
• RAW and LONG RAW Data types

Microsoft SQL Server 2012 - Books Online

• BINARY and VARBINARY Data Types

Dmitry Tolpeko, dmtolpeko@sqlines.com - June 2012