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

Sponsored Links

Tips & Warnings

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.

---

简单来说，当你在oracle中遇到一个clob类型的数据时，可能通过语句

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

将数据取出来，其中，clob_field表示你的clob类型数据的字段名称， 4000表示你要转换成的varchar长度（可根据需要改变），1表示开始转换的位置（如果不想从1开始，可自由调整）。 如效果：