Substring on CLOB field

Question

We are trying to apply substring funciton in CLOB field to trim first 4000 chars. we are getting following error message.

Below is the query we are using:

select
cast(substr(field_name,1,4000) as varchar(4000))
from table_name;

Error report:

SQL Error: ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 8000, maximum: 4000)
22835. 00000 - "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*Cause: An attempt was made to convert CLOB to CHAR or BLOB to RAW, where the LOB size was bigger than the buffer limit for CHAR and RAW types. Note that widths are reported in characters if character length semantics are in effect for the column, otherwise widths are reported in bytes.
*Action: Do one of the following 1. Make the LOB smaller before performing the conversion, for example, by using SUBSTR on CLOB 2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.

Solution 1

Alternative using dbms_lob.substr:

dbms_lob.substr(clob_field_name, desired_size)

Doesn't require cast.

Solution 2

The limit on VARCHAR is 4000 bytes not characters. If your database charset is a multibyte charset substr(field_name,1,4000) will be too long. (The 8000 in the error message suggests that each character is using 2 bytes, so UTF16 maybe ?).

Try :

select cast(substr(field_name,1,2000) as varchar(4000 byte))
from table_name;

But be aware that 2000 characters might take more than 4000 bytes (it depends on your database character set and on the text you're encoding).