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.