ETL CLOB data type

What is CLOB

A Character Large OBject (or CLOB) is a collection of character data in a database management system, usually stored in a separate location that is referenced in the table itself.

A CLOB (character large object) value can be up to 2,147,483,647 characters long. A CLOB is used to store unicode character-based data, such as large documents in any character set.

The length is given in number characters for both CLOB, unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, 1024*1024*1024 respectively.

Length is specified in characters (unicode) for CLOB.

From 8.5 version, The Oracle connector supports reading and writing Oracle LOB data types BFILE,BLOB, CLOB, NCLOB, LONG RAW, RAW, and XMLType.

Example

One of the column in a particular table contains HTML or XML data stored as a CLOB, The length could be large say ‘1048576’.

Use LongVarchar(n) datatype within DataStage.

In ETL job, replace CLOB with LongVarChar of same size (1048576) as CLOB is not defined in data Stage. Where “n” depends on what is in the CLOB field.

For example, DBA/Analyst confirms it will not be more than 1500 chars in the field. Use Varchar(2000) – not LongVarChar.

images stored as CLOBs

BLOBs are generally used to store images. Sometimes though, CLOBs have been used to store images. Sometimes only the Path is stored while the images are physically stored on the disk.

In cases where we can encode the images and store the text and the encoding metadata, we use BLOB/CLOB to store the images.