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.
March 20, 2008 at 12:07 pm
Instead of using jdbc, I am using Hibernate with Java Springs, how can I get the fix implemented in my code.
June 5, 2008 at 3:35 pm
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
June 5, 2008 at 4:19 pm
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.
June 13, 2008 at 3:58 pm
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?
June 12, 2009 at 4:05 pm
I am on 11.1.0.7.0. and its driver.
Still getting the same error.
Any workaround?
July 8, 2009 at 2:55 pm
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.
September 2, 2009 at 6:42 pm
Thanks very much. You saved my day!
February 4, 2010 at 10:53 pm
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
February 4, 2010 at 10:55 pm
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.
March 4, 2010 at 8:48 pm
Hi DAN,
Please give more details , what solved the problem .
October 26, 2010 at 4:32 am
Excellent explanation,Thanks a lot,You saved my day.
February 9, 2011 at 6:44 am
Thanks, this solves a problem troubled me for a few months
February 9, 2011 at 6:48 am
oh forgot to mention that this bug is fixed in 10.2.0.5. It is mentioned in the readme as bug 8847022.
March 28, 2011 at 9:56 am
Thanks for the info..
Helped a lot..
May 31, 2011 at 11:05 am
Hi Vlad,
Thanks a lot for this heads up ! Googled and found this. Saved a day for me 🙂
Thanks again.
– Yohan
September 26, 2011 at 6:42 am
Израиль на сайте об автомобилях.
November 22, 2011 at 9:16 pm
Funny enough, but I have the same error with 11,0.2.0.2 drivers against 11.1.0.7 database.
April 25, 2012 at 12:51 pm
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.
October 17, 2012 at 8:59 am
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.
October 17, 2012 at 9:38 am
Strange… mmmh….
Do you set the property “oracle.jdbc.RetainV9LongBindBehavior” to “true” before creating the DB connection?
May 5, 2013 at 2:45 pm
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!
May 6, 2013 at 8:56 pm
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!
June 3, 2013 at 3:06 am
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!
July 23, 2013 at 7:10 am
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.
July 26, 2013 at 3:17 pm
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.
July 28, 2013 at 12:48 pm
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.
September 20, 2014 at 7:58 pm
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?
😛 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?
September 21, 2014 at 3:23 am
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
September 21, 2014 at 3:25 am
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. . . . . .
September 21, 2014 at 4:51 am
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!
September 21, 2014 at 4:54 am
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. . . . . .
September 21, 2014 at 5:02 am
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
September 21, 2014 at 5:28 am
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!!
September 21, 2014 at 5:31 am
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!
September 21, 2014 at 5:35 am
What a information of un-ambiguity and preserveness of precious knowledge
concerning unpredicted feelings.
September 21, 2014 at 5:45 am
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.
September 21, 2014 at 9:01 am
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.
September 21, 2014 at 1:43 pm
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!
January 2, 2015 at 1:48 am
You post very interesting posts here. Your website deserves much more visitors.
It can go viral if you give it initial boost, i know very useful tool that can help you, simply search in google: svetsern traffic tips
September 13, 2016 at 5:24 am
If you are interested in topic: earn online philippines karaoke – you should read about Bucksflooder first