Not many tables demand the use of CLOBs (Character Large Objects). CLOB data type columns can store up to 4 GB of text.
When designing a new application, developers face a dilemma – I need a column where I need to store a large block of text, should I use multiple VARCHAR2 columns or CLOB? The decision to use VARCHAR2 instead of CLOB is usually driven by the fear of unknown. One should always use CLOB when it comes to making a decision about multiple VARCHAR2 columns vs. CLOB to store a single logical piece of textual data.
Reasons to use CLOB
- “Ridicule me for life” scenario – The author who created an Oracle table with VARCHAR2 columns such as SQL_TEXT1, SQL_TEXT2, SQL_TEXT3 to overcome lengh limitations has opened himself/.herself to ridicule by peers for years to come
- There are still limitations – Since VARCHAR2 columns are limited to 4000 characters max, it still does not solve the problem for extremely large texts
- Tedious to use – Every time a text is needed as a whole, the multiple VARCHAR2 columns have to be concatenated together. Even PL/SQL VARCHAR2 data type only supports up to 32 Kb.
- Hard to maintain – The moment someone realized that SQL_TEXT4 is needed, all the code and query references have to be updated. Single CLOB column is really simple.
- Oracle internally treats inline CLOB values less than 4000 characters as VARCHAR2
- Performance – Indexing and appropriately using multiple VARCHAR2 columns is difficult at best and impossible at worst. Indexing CLOBs are slightly different from regular columns though.
When to use what datatype?
Oracle, for its part, has slowly migrated away from LONG data type to CLOB on its system tables. Choose between these data types based on your need for your applications
|CLOB||Use this to store large ANSI text|
|NCLOB||Same as CLOB except that characters are stored in multi-byte character set. Uses twice the space as CLOB to store the same thing when used inappropriately.|
|BLOB||Binary large objects – like images, videos or anything that is binary and large in nature.|
|XMLType||Use this to store XML data. Oracle has rich support for querying and updating XML type columns using SQL.|
|VARCHAR2||It is well known that the column will NEVER ever need to store more than 4000 characters max|
|NVARCHAR2||Similar to CLOB vs. NCLOB difference above|
|BFILE||Data is stored in a binary file on the operating system file system instead of inside the Oracle database files|
Once the data is stored in a CLOB column for example, querying and using the data may require that the CLOB be converted to VARCHAR2 to make it more human-readable or when we need to using functions that are available only for the VARCHAR2 data type.
CLOB output (unreadable)
Normally, when querying using commonly available tools, CLOBs would look like this:
SELECT * FROM dba_hist_sqltext
CLOB output (readable using DBMS_LOB)
To convert them to VARCHAR2, use the DBMS_LOB package:
SELECT a.*, dbms_lob.substr( sql_text, 4000, 1) sql_text_vc FROM dba_hist_sqltext a
|314195459||0rc4km05kgzb9||(HUGECLOB)||3||select 1 from obj$ where name=’DBA_QUEUE_SCHEDULES’|
|314195459||39m4sx9k63ba2||(HUGECLOB)||3||select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#|
|314195459||d3g73kux34mca||(HUGECLOB)||3||with temp_kk as (select code fk_business_unit, description processing_group from gotm_lookup_ext gle|
SUBSTR works but stick with DBMS_LOB
Even a regular SUBSTR works fine with CLOB’s. It does need to be converted with TO_CHAR to be human-readable as text. The following will produce the same result as above although using the DBMS_LOB supplied functions is the recommended and safe way to work with LOB’s.
SELECT a.*, TO_CHAR(SUBSTR( sql_text, 1, 4000)) sql_text_vc FROM dba_hist_sqltext a
In the above example, only the first 4000 characters are fetched. However, it is easy to alter the third parameter, the starting position and fetch other parts of the text.
In fact, the DBMS_LOB package offers a lot of functionality to work with large object data types. Please read about it here. Additional information on working with LOB’s is also available at Oracle Docs.