ORA-01461: can bind a LONG value only for insert into a LONG column

Just one another issue with JDBC I think is worth mentioning. You perhaps wonder why so obvious error message could be troublesome to comprehend. Unfortunately, it speaks of things that a developer never meant to happen. It doesn’t have to be a LONG value. This error could appear when ASCII strings are bound for VARCHAR2 columns. In this case it happens when JDBC v. 10.1/2 is used with a Oracle Server 9i database configured for multi-byte character set. Before going any further, please note that according to support matrix Oracle JDBC 10g and 9i are cross supported with Oracle Server 9i and 10g.

For the testing I used two databases Oracle 10g and Oracle 9i with database character set UTF8 and JDBC 10.2.0.3.

Some of the Asian language characters can consume up to 3 bytes in UTF8 encoding. But can you expect that a string composed of 1334 ASCII characters would not bind for VARCHAR2(4000)? However, it is possible. Let’s dig up little more details.

Here is how the string of 4000 “a” characters would appear to the Oracle 9iwhen bound from JDBC 10g with default properties. This bind is not spooled into the 10046 trace file. Instead it can be captured by enabling stacktrace dump on 1461 event.


 bind 19: dty=1 mxl=4001(12000) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=4000 offset=0
   bfp=ffffffff7cd7f060 bln=4000 avl=00flg=05

Oracle Server 10g. Note, that when you test this scenario, there are no errors – Oracle Server 10g  is perfectly fine with the 12000 bytes in the private buffer.


 Bind#19
  oacdty=01 mxl=4001(12000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=871 siz=4000 off=0
  kxsbbbfp=ffffffff7b55a0c0  bln=4000  avl=4000  flg=05
  value="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa....

According to Metalink Note: 3756847.8 this problem was introduced in some 10.1 versions of JDBC, but was fixed in 10.2. However, it is not enabled by default. Following JDBC connection property enables the fix.


java.util.Properties props = new java.util.Properties();
props.put("oracle.jdbc.RetainV9LongBindBehavior","true");
ods.setConnectionProperties(props);
ods.setURL(url);

And here is how the bind looks like after the fix is enabled.
 

Oracle Server 9i:


bind 19: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=03 oacfl2=10 size=4000 offset=0
   bfp=ffffffff7cd56088 bln=4000 avl=4000 flg=05
   value="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...

Oracle Server 10g:


 Bind#19
  oacdty=01 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=871 siz=4000 off=0
  kxsbbbfp=ffffffff7b56f060  bln=4000 avl=4000flg=05
  value="aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...

There is no need in enabling this fix for 10g/10g setup. 10g version of the trace is shown just for comparison purposes.

In conclusion I want to clarify that this article describes the unexpected behavior with legitimate length values. However, there are other cases when apparently correct values fail to insert with same message. I.e. already mentioned strings that, when using UTF8 for database character set, can expand from 4000 Asian language characters to up to 12000 byte values. In these cases, Notes: 241358.1, 370438.1 recommend different workarounds. Note: 445072.1  has decent example of getting the length of encoded string and suggests converting VARCHAR2 columns into CLOB if encoded string length exceeds maximum length of VARCHAR2 type.

About these ads

38 Responses to “ORA-01461: can bind a LONG value only for insert into a LONG column”

  1. Gaurav Says:

    Instead of using jdbc, I am using Hibernate with Java Springs, how can I get the fix implemented in my code.

  2. Loïc Lefèvre Says:

    For your information, the 10.2.0.4 driver is rolled out. Do you encounter the same problems with this version?

    Regards,
    Loïc Lefèvre

  3. Vlad Sadilovskiy Says:

    Loïc Lefèvre, thanks for pointing this out. We are in the process of testing few applications on 10.2.0.4 Oracle patchset. And this is one of the tests we planned to do. I will comment on it once it is done.

  4. Aniceto Perez Says:

    I’ve tried this with drivers ver 10.2.0.3, 10.2.0.4 and always the same error:

    update mytable (keyFileld, xmltypeField) values (?, xmltype(?))

    If the xml content is not longer than 4K, it works.

    Any ideas?

  5. Vijay M Says:

    I am on 11.1.0.7.0. and its driver.
    Still getting the same error.

    Any workaround?

  6. Alexandru Barbat Says:

    I have the same issue on websphere 7.0 with 11.1.0.7.0-Production driver(ojdbc6.jar)
    On tomcat 6.0.18 i don’t get the error even if use the 11.1.0.7.0 or 9.0.2.0.0 driver version.

    I use the same database for each of those two cases.

  7. sgaur Says:

    Thanks very much. You saved my day!

  8. Dan Says:

    Try upgrading any of your existing ojdbc14 and/or related orai18n JAR’s to the most recent versions from Oracle.

    We recently ran into a problem with Hibernate and using Oracle’s JDBC driver JAR’s, that was using older v. 10.1.0.2.0 of these JAR files, and fixed an ORA-03106 with new versions 10.2.0.4.0

  9. Dan Says:

    Also we didn’t have to enable the oracle.jdbc.RetainV9LongBindBehavior property, the new JAR’s versions seem to have a bug fixed in them for the problem we were running into with Oracle’s JDBC and NLS related JAR files.

  10. Slict Says:

    Hi DAN,

    Please give more details , what solved the problem .

  11. Rajesh Arumugam Says:

    Excellent explanation,Thanks a lot,You saved my day.

  12. Anonymous Says:

    Thanks, this solves a problem troubled me for a few months

  13. Anuradha Says:

    Thanks for the info..
    Helped a lot..

  14. Yohan Says:

    Hi Vlad,

    Thanks a lot for this heads up ! Googled and found this. Saved a day for me :)

    Thanks again.

    – Yohan

  15. RobertSA Says:

    Израиль на сайте об автомобилях.

  16. al0 Says:

    Funny enough, but I have the same error with 11,0.2.0.2 drivers against 11.1.0.7 database.

  17. Giulio Says:

    I confirm, database = 11.2.0.1.0 + driver = 11.2.0.1.0 and I still get the same bloody error…
    I highly suspect that this strange behavior is related to to the value of the NLS_LENGTH_SEMANTICS property of Oracle.
    I mean… a VARCHAR2 of 4,000 bytes cannot contains 4000 characters when UTF8 (or similar) encoding is used. This could explain why this error is raised even if the field contains less characters of the maximum allowed.
    I repeat, this is only my suspect.

    • Nissim Says:

      Hi,

      I had this ORA too and don’t not why, I get it when I try to insert into a VARCHAR2(4000 CHAR) column value that have more than 3,993 chars.

      • Giulio Says:

        Strange… mmmh….
        Do you set the property “oracle.jdbc.RetainV9LongBindBehavior” to “true” before creating the DB connection?

  18. Brook Says:

    Please let me know if you’re looking for a article writer for your site. You have some really great articles and I believe I would be a good asset. If you ever want to take some of the load off, I’d absolutely love to write some articles for your blog
    in exchange for a link back to mine. Please send me an email if interested.
    Many thanks!

  19. Ezra Says:

    Hello would you mind letting me know which web host you’re utilizing? I’ve loaded your blog in 3 different web
    browsers and I must say this blog loads a lot faster then most.
    Can you suggest a good web hosting provider at a honest price?
    Kudos, I appreciate it!

  20. http://www.sodrujestvo.az/?p=123 Says:

    It’s a pity you don’t have a donate button! I’d certainly donate to this excellent blog! I guess for now i’ll settle for book-marking and adding your RSS feed
    to my Google account. I look forward to fresh updates
    and will talk about this website with my Facebook group. Talk soon!

  21. wordpress developer Says:

    For tips and advice and to discover the best
    way to create a Word – Press MLM website, go to his website: Wealth Success Ventures.
    Internet users are an impatient lot – such is a given fact.
    Once its done (shouldn’t take long at all) click the Activate Plugin link and the plugin will become live.

  22. black teas Says:

    Its such as you learn my thoughts! You seem to grasp a lot about
    this, like you wrote the ebook in it or something. I feel that you simply can
    do with some percent to pressure the message house a little bit, however other than that, this is magnificent
    blog. An excellent read. I will definitely be back.

  23. quibids reviews Says:

    A further issue is that video gaming became one of the all-time
    most significant forms of excitement for people of
    any age. Kids play video games, and adults do,
    too. The XBox 360 is among the favorite gaming systems for folks who love to
    have a lot of activities available to them, as well as who like to relax and play live with other folks all
    over the world. Thank you for sharing your ideas.

  24. www.youtube.com Says:

    I seldom leave comments, however after reading a lot of responses on this
    page ORA-01461: can bind a LONG value only for insert into a LONG column | Observing Oracle.
    I actually do have 2 questions for you if you do not mind.
    Could it be only me or does it look like some of these responses look like they are written by brain dead visitors?
    :-P And, if you are writing on other sites, I would like to
    keep up with anything new you have to post. Could you list of all of your public pages like your twitter feed,
    Facebook page or linkedin profile?

  25. Pelican Kayak Says:

    Hey! Would you mind if I share your blog with my facebook group?
    There’s a lot of people that I think would really appreciate your content.
    Please let me know. Thank you

  26. www.youtube.com Says:

    Hiya very cool site!! Guy .. Beautiful .. Amazing .. I’ll bookmark your web site and take the feeds additionally?
    I am glad to seek out so many useful information here within the
    publish, we want work out more strategies in this regard,
    thank you for sharing. . . . . .

  27. hurricane kayaks santee 116 sport kayak Says:

    Today, I went to the beach front with my kids. I found
    a sea shell and gave it to my 4 year old daughter
    and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed.
    There was a hermit crab inside and it pinched her ear. She never wants to go back!
    LoL I know this is entirely off topic but I had to tell someone!

  28. www.youtube.Com Says:

    Hi there very cool web site!! Man .. Excellent .. Superb ..
    I will bookmark your website and take the feeds also?
    I’m satisfied to find so many useful information here within the submit, we’d like work out extra techniques in this regard, thank
    you for sharing. . . . . .

  29. carbon fiber kayak Says:

    I think this is among the most important information for me.
    And i’m glad reading your article. But should remark on some general things,
    The web site style is great, the articles is really excellent :
    D. Good job, cheers

  30. Cheap Kayaks Says:

    Hi, i think that i noticed you visited my website so i came to return the favor?.I’m trying to in finding issues to improve my
    site!I guess its good enough to use some of your ideas!!

  31. kayak outfitting Says:

    You actually make it seem so easy with your presentation but I
    find this matter to be actually something which I think
    I would never understand. It seems too complex and extremely
    broad for me. I am looking forward for your next post, I’ll try to get
    the hang of it!

  32. youtube.com Says:

    What a information of un-ambiguity and preserveness of precious knowledge
    concerning unpredicted feelings.

  33. kayak anchor reel Says:

    Nice post. I was checking constantly this blog and I’m impressed!
    Very useful information specifically the last part :) I care for such info much.
    I was seeking this certain information for a
    long time. Thank you and best of luck.

  34. kayak Prices Says:

    Hmm is anyone else experiencing problems with the
    pictures on this blog loading? I’m trying to find out if its a problem on my end
    or if it’s the blog. Any suggestions would be greatly appreciated.

  35. www.youtube.com Says:

    Right here is the perfect blog for anybody who would like to find out
    about this topic. You realize so much its almost tough to argue with you (not that I actually would want to…HaHa).
    You certainly put a fresh spin on a topic which has been discussed for a long time.
    Wonderful stuff, just wonderful!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: