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?

What do you not understand? Max. length of NVARCHAR2 is 2000 characters which is exceeded. — Wernfried Domscheit
@WernfriedDomscheit: yes, but do you have any suggestion on how to bypass this restriction? — janez_007
No, a NVARCHAR2 must not be longer than 2000 - why do you think you can bypass it? btw, why do you try to convert a NCLOB to NVARCHAR2 at all? — Wernfried Domscheit
I will be "reading" the data with a tool, that cannot "read" from NCLOB, which is why I would like to replace the NCLOBs with VARCHAR2, even though the text will be truncated. btw, as per this doc, the max len of VARCHAR2 is 4000 link — janez_007
It might be 4000 Bytes which is 2000 characters for Multibyte NCLOB where each character uses at least 2 Bytes. Did you try substr(TO_NCHAR(NCLOB_FIELD),1,1000)? — Wernfried Domscheit

Jon Heller Jon Heller · Accepted Answer · 2018-06-16T01:57:00

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

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