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

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

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:

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.

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.

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.

• Oracle to SQL Server Migration Knowledge Base
• Oracle to SQL Server Data Type Mapping

• Converting Oracle LENGTH to SQL Server LEN

Oracle 11g R2 SQL Language Reference

• CLOB Data Type
• LENGTH Function
• DBMS_LOB.GETLENGTH Function

Microsoft SQL Server 2012 - Books Online

• Constants
• LEN Function
• DATALENGTH Function

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.