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.
Start Oracle's SQL*Plus program.
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;
Press "Enter." SQL*Plus executes the statement.