How to Convert From Clob to Varchar2 in Oracle

The substr function converts Oracle clob data to short text strings.
Image Credit: Stockbyte/Stockbyte/Getty Images

The Oracle database software offers a few different data types with which to store character string data: char, varchar2 and clob. The char data-type fields have a fixed length, varchar2 fields have a variable length and clob fields let you store up to 128 TB of large blocks of text. While varchar2 fields have a limit of 4,000 characters, you can convert up to 4,000 characters from a clob field into a varchar2 format field using Oracle's dbms_lob.substr function. The function extracts a substring of the clob field, effectively turning it into a varchar2 field.

Step 1

Start Oracle's SQL*Plus program.

Step 2

Type the following statement into SQL*Plus:

SELECT dbms_lob.substr( clob_field, 4000, 1 ) FROM my_table;

For "clob_field," substitute the name of the clob field you want to convert to varchar2. Substitute the name of your data table for "my_table." For example, if the clob field name is "budget_memo" and the table's name is "budgets," type the following statement:

SELECT dbms_lob.substr( budget_memo, 4000, 1 ) FROM budgets;

Step 3

Press "Enter." SQL*Plus executes the statement.


The dbms_lob.substr function has three parameters: the field name, the number of characters you want to convert and the position from which the conversion begins. The expression, dbms_lob.substr( clob_field, 4000, 1 ) converts 4,000 characters of clob field beginning with the first. Use a smaller number in place of 4,000 if you want to convert fewer characters, and use a number larger than one to shift the starting point of the conversion.