小木屋

       童话般的故事，童话般的风景，童话般的心。在狐狸的引领下，顺着小女孩的视野，我们看到和听到了一曲优美悦目的森林之歌。那些无知无畏的历险、孩子气的自语、会心的微笑、风过林间的呢喃、飞舞花间小昆虫、笨棕熊和笨刺猬，简直就是一首大自然的诗。

      多谢肥星的推荐，影片下载很长时间了，今天才静下心来细细地看了一遍。那一幕幕拨动心弦的美丽画面，值得我酝酿已久的期待。

      其实我觉得电影和《小王子》没什么关系啦，除了一点，它们都是我所喜爱的。

      最近项目中需要在一个表的字段中插入长的字符串，数据表中该字段的类型是varchar2(4000)，结果发现，当字符串长度在1000～2000之间的时候，就会报错，而当字符串长度在1000以下或者2001～4000的时候都没问题，显然这是Oracle的一个bug。错误信息如下：

15:59:46,953 ERROR [JDBCExceptionReporter] Could not execute JDBC batch update
java.sql.BatchUpdateException: ORA-01461: 仅可以为插入 LONG 列的 LONG 值赋值

at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10656)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeBatch(DelegatingPreparedStatement.java:231)
at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:126)
at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:59)
at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:56)
at net.sf.hibernate.impl.BatcherImpl.prepareBatchStatement(BatcherImpl.java:109)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:460)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:442)
at net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:29)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2418)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2371)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2240)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:386)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:314)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:189)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:134)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:148)
at $Proxy121.saveUserAuth(Unknown Source)

    如果把insert或者update语句直接在PL/SQL里面跑，或者拼成sql语句在程序里执行，都是没问题的，但是如果是用ojdbc驱动的方法（包括hibernate），就会出错。此外，在另外一台Linux的机器上的Oracle9.2.0.8就没问题（生产机是Windows系统，Oracle9.2.0.1）。

    经过google和多个交叉测试，排除了操作系统、中间件平台（Weblogic）的问题。在红伟的指点下，找到这么一个网页，把这个问题的原因说得好像比较清楚（wordpress的，需要翻墙）：http://vsadilovskiy.wordpress.com/2007/10/19/ora-01461-can-bind-a-long-value-only-for-insert-into-a-long-column/

    里面提到的解决办法是，用新版的ojdbc驱动，然后把OracleDatasource的oracle.jdbc.RetainV9LongBindBehavior属性设为true，经测试，并不生效。测试了从Oracle网站下载的不同版本（9i，10g各小版本）的ojdbc.jar，情况依旧。

      最后，把生产机的Oracle升级到9.2.0.8，问题解决。

------------------------------------------------------------

附：上面提到的网页的文字拷贝

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

October 19, 2007 — Vlad Sadilovskiy

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.

    基本上，我赞成里面的每一个字。

《明星秀》

《重建需要为人民服务》

      窗外下起了雨，醒过来发现就睡不着了。在万恶的金水湾呆了两周，回到家里居然睡不好，真是难以想象。

       前几天，天涯blog的所有文章在google reader里都变成了未读，结果不小心又看了一遍筱璐的《江月心路》，忽然很怀念那些在路上的时光。前几个月，纷繁复杂的事情一件压一件，让人喘不过气。在为数不多的休息时间，为了让自己不要老是想着项目的事，见缝插针地看NBA，CBA，英超，打篮球和羽毛球，看图书馆借来的书，所有日程都排得满满，也就没有了胡思乱想的时间。

      这周在金水湾封闭培训和考试，上不了内网，反而整个世界清静了，每天就是复习、吃饭、睡觉，外加打球、上网和看比赛，于是思绪常常不受控制，不时飘向那去过和没去过的远方。

      《大梦敦煌》的演出虽然俗套，却依然让我怀念飞天长袖般柔美的沙丘曲线，及和田美玉般澄净的月牙泉水。

      西北莜面庄摆卖的蓝莓酱，使我忆起恩和那视觉和味觉的盛宴。

      读luxx的花叶繁茂鼓浪屿，眼前似乎又出现了那树影斑驳的老房子，耳边似乎又响起了那若断若续的钢琴声。

      看到磨房提到的四姑娘山救援事件，不禁晒笑自己第一次高原徒步时的懵懂无知，同时也庆幸当时找到了好向导和好队友。

      电脑墙纸用卡瓦格博的照片，是因为日照金山那一刻无法用言语描述的震撼，将令我永世难忘。

      而梅里外转，那长如一生的七天，七天里攒下的勇气和微笑够用一生。

      旅途的记忆无时无刻不在我的心底，只是看什么时候被触动，而已。

      还有冈仁波齐，世界的中心，当在《Wild China》上看到那些壮丽的画面时，我内心的激动总是无法抑止。只是，何时才能到达你的身边。