• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java.sql.SQLException: ORA-22835: Buffer too small for CLOB to CHAR or BLOB

 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to insert clob data type. Sometime its work and sometime its throw the subject error. Please advise



Thanks in anticipation
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello again,

Please help me if possible

Best regards
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
At which line of your code does the exception occur?

This error indicates an attempt to convert CLOB to VARCHAR failed because the text in the CLOB was too long (this explains it fails only sometimes -- when the text is too long). Aren't you trying to insert the created CLOB into a VARCHAR column?
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to insert clob type data into clob column not varchar type column. This was running fine but suddenly this message started
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Does the error occur at the INSERT statement? Can you post the code containing the insert statement, and ideally the table description (output from DESCRIBE TABLE) so that we can see the full path the CLOB takes from its creation?

It is possible to create a CLOB in Oracle directly in the table, using empty_clob() function, and populate it afterwards. That would prevent any conversions, but make your code Oracle specific.
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 20 20:25:17 2013

Copyright (c) 1982, 2010, Oracle. All rights reserved.

SQL> desc cvs
Name Null? Type
----------------------------------------- -------- ----------------------------

CV_ID NOT NULL NUMBER(14)
USER_ID NUMBER(14)
CAT_ID NUMBER(14)
COUNTRY NUMBER(14)
CVDATE TIMESTAMP(0)
ORIGINALCV VARCHAR2(50)
KEYWORDS VARCHAR2(3950)
CVHEADLINE VARCHAR2(90)
TOTALEXPERIENCE NUMBER(7)
OBJECTIVES VARCHAR2(3900)
NAME VARCHAR2(90)
FATHERNAME VARCHAR2(90)
MOTHERNAME VARCHAR2(90)
DOB VARCHAR2(14)
GENDER VARCHAR2(25)
MSTATUS VARCHAR2(50)
PASSPORTNO VARCHAR2(50)
EMAIL VARCHAR2(230)
PHONE VARCHAR2(90)
MOBILE VARCHAR2(90)
ADDRESS VARCHAR2(2500)
STATE VARCHAR2(90)
ZIPCODE VARCHAR2(36)
CITY VARCHAR2(36)
NATIONALITY VARCHAR2(50)
EDUCATION CLOB
EXPERIENCE CLOB
SKILLS CLOB
LANGUAGES VARCHAR2(1400)
HOBBIES VARCHAR2(3200)
ACHIEVEMENTS VARCHAR2(3900)
REFERENCES VARCHAR2(3900)

SQL>
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So they are definitely CLOBs. Can you post the INSERT statement? Does the exception occur on the line that executes the INSERT statement?
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:So they are definitely CLOBs. Can you post the INSERT statement? Does the exception occur on the line that executes the INSERT statement?


 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not at my computer right now to test it, but I'd suggest using setClob() instead of setObject(). Although setObject() should work, according to the documentation, I'd say this is worth a try.

You might also want to make sure you're using the latest version of the Oracle's JDBC driver.


Ignore the above. Please check the order of column in your insert statement. You're setting the CLOBs as the last three parameters, but the last three columns are VARCHARs, not CLOBs.
 
Farakh khan
Ranch Hand
Posts: 833
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:Please check the order of column in your insert statement. You're setting the CLOBs as the last three parameters, but the last three columns are VARCHARs, not CLOBs.


Wow, you are genuis. Yes I set last three parameters varchars instead of clobs. I changed the sequence of column and its inserted without any problem

Thanks from the bottom of my heart. Appreciated!!
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need to set the parameters in the order corresponding to the columns listed in the INSERT INTO statement. Last three columns in your statement are hobbies, achievements and references. The table description you've posted shows these columns are of the VARCHAR2 type. The database then tries to convert CLOB to VARCHAR2 and this fails when the maximum VARCHAR2 length is exceeded.

In my opinion, the real bug in your code is that you're inserting wrong values into the respective columns. The column order and the parameter order don't match.

Edit: you've figured it yourself before I've finished my reply. But the next paragraph probably still applies.

Furthermore, you're setting numeric values (eg. user_id or cat_id) using setString(). This causes implicit conversion from varchar to numeric value in the database, and might prove to be problematic if your database locale changes. Always set the parameters in the most precise type possible - in this case, probably a setLong() would be appropriate. See avoiding implicit conversions for further information.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're welcome!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic