NCLOB to VARCHAR2

Question

I am trying to convert a NCLOB to VARCHAR2 in Oracle, but get the following error:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 2669, maximum: 2000)

Here is the code that I am using:

select substr(TO_NCHAR(NCLOB_FIELD),1,3800)
from TABLE

Any way around this error?

Answer 1

Put the SUBSTR before the conversion and use a smaller size:

select TO_NCHAR(substr(NCLOB_FIELD,1,2000))
from TABLE;