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, Jun 14 '18 at 11:21
@WernfriedDomscheit: yes, but do you have any suggestion on how to bypass this restriction? — janez_007, Jun 14 '18 at 11:47
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, Jun 14 '18 at 11:49
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](https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330) — janez_007, Jun 14 '18 at 11:56
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, Jun 14 '18 at 14:04
yes, I did try it. The error kicks in before the SUBSTR at the TO_NCHAR function part, so even `substr(TO_NCHAR(NCLOB_FIELD),1,1)` gives an error... — janez_007, Jun 14 '18 at 15:45
@BrianLeach: yes, the DBMS_LOB.SUBSTR work for my CLOB fields, but does not seem to work for the NCLOB. I get the following error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small — janez_007, Jun 18 '18 at 12:18

score 2 · Accepted Answer · answered Jun 16 '18 at 01:57

2

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

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

answered Jun 16 '18 at 01:57

Jon Heller

1 Answers1