CLOB Data Type - Oracle to SQL Server Migration

In Oracle, CLOB data type stores variable-length character data (character large object) in the database character set that can be single-byte or multibyte (supports more than 4 GB).

In SQL Server, you can use VARCHAR(max) or NVARCHAR(max) to store stores variable-length character data up to 2 GB.

Oracle:

  -- Create a table with CLOB column
  CREATE TABLE countries
  (
     name VARCHAR2(90),
     notes CLOB
  );
 
  -- Insert some data
  INSERT INTO countries VALUES ('Greece', 'Greece is a country in south-east Europe. Athens is the capital...');
  # 1 row created.

SQL Server:

  -- Create a table with VARCHAR(max) column
  CREATE TABLE countries
  (
     name VARCHAR(90),
     notes VARCHAR(max)
  );
 
  -- Insert some data
  INSERT INTO countries VALUES ('Greece', 'Greece is a country in south-east Europe. Athens is the capital...');
  # 1 row(s) affected

CLOB Data Type - Conversion Overview

Conversion summary:

Oracle SQL Server
Syntax CLOB VARCHAR(max)
Maximum Size 4 GB * db_block_size 2 GB
String Literals in DML
Insert Empty Value EMPTY_CLOB() '' (Empty string constant)
Get Length LENGTH() In characters LEN() In characters
DBMS_LOB.GETLENGTH() DATALENGTH() In bytes
Character Set Database
Transaction Support
Alternatives NCLOB UTF-8 or UTF-16 NVARCHAR2(max) Unicode UCS-2
LONG Deprecated TEXT Deprecated
NTEXT Unicode UCS-2, deprecated

CLOB Data Type - Conversion Details

In Oracle, you can specify a string literal in SQL INSERT statement to insert data into a CLOB column. You can also insert an empty value using EMPTY_CLOB() function, and you can insert NULL.

Note that if you use '' (empty string) in INSERT statement, Oracle will insert NULL:

Oracle:

  -- Using a string literal to insert a value to a CLOB column
  INSERT INTO countries VALUES ('Monaco', 'Monaco is a sovereign city state, located on the French Riviera...');
 
  -- Insert an empty value
  INSERT INTO countries VALUES ('United Kingdom', EMPTY_CLOB());
 
  -- Insert a NULL
  INSERT INTO countries VALUES ('United States', NULL);
 
  -- Insert '', NULL will be inserted
  INSERT INTO countries VALUES ('France', '');

Table content in Oracle:

In SQL Server, you can also specify a string literal to insert a value into a CLOB column. To insert an empty value you can just use '' (empty string constant).

Note that you may need to replace '' used in INSERT statements for Oracle with NULL in INSERT statements for SQL Server to get the same results as Oracle:

SQL Server:

  -- Using a string literal to insert a value to a VARCHAR(max) column
  INSERT INTO countries VALUES ('Monaco', 'Monaco is a sovereign city state, located on the French Riviera...');
 
  -- Insert an empty value
  INSERT INTO countries VALUES ('United Kingdom', '');
 
  -- Insert a NULL
  INSERT INTO countries VALUES ('United States', NULL);
 
  -- If Oracle statement used '' replace it with NULL for SQL Server to insert NULL
  INSERT INTO countries VALUES ('France', NULL);

Table content in SQL Server:

Get the Length of CLOB

In Oracle, you can use LENGTH() or DBMS_LOB.GETLENGTH() function to get the length of a CLOB column in characters.

There is LENGTHB() function to get the length in bytes, but it can be used for single-byte CLOBs only, so it will return the same result as LENGTH().

Oracle:

  -- LENGTB cannnot be applied for UTF-8 CLOB
  SELECT name, LENGTHB(notes) FROM countries; 
  # ORA-22998: CLOB or NCLOB in multibyte character set not supported
 
  -- Get the length of a CLOB column (UTF-8 character set is used)
  SELECT name, LENGTH(notes), DBMS_LOB.GETLENGTH(notes) FROM countries;

You can see that both LENGTH() and DBMS_LOB.GETLENGTH() return 0 for empty CLOB and NULL if the CLOB is NULL:

In SQL Server, you can use LEN() function to get the length of a VARCHAR(max) column in characters, or DATALENGTH() function to get the length in bytes:

SQL Server:

  -- Get the length of a VARCHAR column
  SELECT name, LEN(notes) AS 'In Characters', DATALENGTH(notes) AS 'In Bytes' FROM countries;

Similar to Oracle, both LEN() and DATALENGTH() return 0 for empty value, and NULL if the VARCHAR(max) is null:

Note that SQL Server LEN does not include trailing spaces. For more information, see Converting Oracle LENGTH to SQL Server LEN.

How to Define Database Character Set Used for CLOB

In Oracle, CLOB columns are stored in the database character set that defines how many bytes can be used to store a single CLOB character.

You can use the following query to define which database character set is used by your Oracle database for CHAR, VARCHAR and CLOB data types:

Oracle:

  -- Define the database character set
  SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
  # AL32UTF8

In this example, UTF-8 was returned (this is the default for Oracle 11g). Also in Western European countries many Oracle installations use WE8ISO8859P1 (Latin-1) as the database character set.

CLOB Storage

If you enable ENABLE STORAGE IN ROW option (this is the default), Oracle stores CLOB data up to 4,000 bytes inline. Data greater than 4000 bytes are always stored externally and you can specify a different tablespace and storage characteristics.

Resources

SQLines Services and Tools

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.

You could leave a comment if you were logged in.