EMPTY_BLOB Function - Empty BLOB Value - Oracle to SQL Server Migration

In Oracle, EMPTY_BLOB() function allows you to assign an empty value (with 0 length) to a BLOB column. Although the length is 0, the value is not NULL.

In SQL Server, you can use 0x constant (empty binary string).

Let's create a table with a BLOB column in Oracle and insert data:

Oracle:

   -- Use EMPTY_BLOB in DEFAULT clause
   CREATE TABLE countries
   (
       name VARCHAR2(90),
       flag BLOB DEFAULT EMPTY_BLOB()
    );
 
   -- Insert a row with the default value:
   INSERT INTO countries (name) VALUES ('France');
 
   -- You can also use EMPTY_BLOB() in INSERT
   INSERT INTO countries VALUES ('United States', EMPTY_BLOB());

Note that if you try to insert '' (empty string constant) explicitly, Oracle inserts NULL:

   -- Try to insert '' (NULL will be inserted)
   INSERT INTO countries VALUES ('United Kingdom', '');
   -- Try to insert NULL
   INSERT INTO countries VALUES ('Germany', NULL);

Now if we query the table, we will have the following result:

  -- Retrieve the length of the LOB column using LENGTH and DBMS_LOB.GETLEGTH functions
  SELECT name, flag, LENGTH(flag), DBMS_LOB.GETLENGTH(flag) 
  FROM countries;

Result:

In SQL Server, you can use 0x constant (empty binary string) instead of EMPTY_LOB() function. Let's create a table with a VARBINARY(max) column in SQL Server:

SQL Server:

   -- Use 0x constant in DEFAULT clause
   CREATE TABLE countries
   (
       name VARCHAR(90),
       flag VARBINARY(max) DEFAULT 0x
    );
 
   -- Insert a row with the default value:
   INSERT INTO countries (name) VALUES ('France');
 
   -- You can also use 0x constant in INSERT
   INSERT INTO countries VALUES ('United States', 0x);

Note that if you try to insert '' (empty string constant) explicitly, the statement fails as a casting is required ('' will be replaced by 0x):

   -- Try to insert '' (NULL will be inserted)
   INSERT INTO countries VALUES ('United Kingdom', '');
   # Msg 257, Level 16, State 3, Line 1
   # Implicit conversion from data type varchar to varbinary(max) is not allowed. 
 
   -- With casting it is ok, but 0x will be inserted
   INSERT INTO countries VALUES ('United Kingdom', CAST('' AS VARBINARY));
 
   -- Try to insert NULL
   INSERT INTO countries VALUES ('Germany', NULL);

Now if we query the table, we will have the following result:

  -- Retrieve the length of the LOB column
  SELECT name, flag, LEN(flag) AS length
  FROM countries;

Result:

To get the same results as Oracle, you can replace '' with NULL in INSERT statements.

• Oracle to SQL Server Migration Knowledge Base
• Oracle to SQL Server Built-in Functions Mapping
• EMPTY_CLOB Conversion

Oracle 11g R2 SQL Language Reference

• EMPTY_BLOB Function

Microsoft SQL Server 2012 - Books Online

• Constants

SQLines offers services and tools to help you migrate Oracle databases and applications to Microsoft SQL Server and SQL Azure. For more information, please Contact Us.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - September 2012.