0

I am getting error ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4918, maximum: 4000) on executing below query (both in hibernate and sql editor).

UPDATE TABLE set COLUMN = UNISTR(COLUMN) WHERE ID = ID;

COLUMN is of type NCLOB

I have tried to use TO_CLOB(UNISTR(COLUMN)) and it didn't work for me.

How can I get rid of this?

PN: I am using Oracle 10.x and Hibernate 3.5.x

I have gone through http://www.dba-oracle.com/t_convert_blob_varchar_datatype.htm. However, I am not able to understand how I can do something similar with UPDATE statement and UNISTR function.

Maz
  • 653
  • 12
  • 22
  • This is happening because `UNISTR` expects a character literal. Casting doesn't work as the `NCLOB` column is bigger than 4000 byte. A text literal can have a maximum length of 4000 bytes in SQL (and 32k in PLSQL) – Gurwinder Singh Jan 17 '17 at 04:35
  • Yes. So what is the way to achieve it? – Maz Jan 17 '17 at 04:37
  • What do you try to achieve, i.e. what is your problem? Usually [UNISTR](http://docs.oracle.com/database/121/SQLRF/functions240.htm#SQLRF06154) function is used to create single characters (similar to [CHR](http://docs.oracle.com/database/121/SQLRF/functions027.htm#SQLRF00616) function). Maybe you are looking for function [TO_NCLOB](http://docs.oracle.com/database/121/SQLRF/functions226.htm#SQLRF06139). – Wernfried Domscheit Jan 17 '17 at 07:12
  • I have special characters in the clob that needs to be UNISTR'd. Using the above update query, it gives error. Also, as I mentioned, TO_CLOB isn't working. – Maz Jan 17 '17 at 19:08

0 Answers0