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

26 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.


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: