How to Convert From Clob to Varchar2 in Oracle

How to Convert From Clob to Varchar2 in Oracle

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 “budg