Tuesday, June 21, 2011

streamstolob=false caused ORA-01407

When insert large amount of characters into CLOB column of one Oracle db table, iBatis will throw ORA-01407 exception
--- The error occurred in conf/sqlmaps/maps/EmailTemplate.xml. 
--- The error occurred while applying a parameter map. 
--- Check the UpdateSiteEmailTemplate-InlineParameterMap. 
--- Check the statement (update failed). 
--- Cause: com.inet.ora.Ora4SQLException: [Oracle] #56 ORA-01407: cannot update ("TEST"."TESTTABLE"."MESSAGE") to NULL
[Oracle] #56
        at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
        at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)

Root Cause:
By default, inet JDBC driver implemented property streamstolob is set to false. Change stream parameter to CLOB/BLOB parameter to make it compatible with this data types. Without it, streams are only compatible until 4K bytes. Note: This property will work for i-net SERO, i-net SEROPTO and i-net ORANXO only.

For more info, check out i-net JDBC Driver Manual (from http://www.inetsoftware.de/)

To use BLOB or CLOB data types larger than 4 KB for persistence using the Inet Oraxo JDBC Driver for Oracle Databases, you must set the database's streamstolob property value to true. There are two ways to set this property:
  1. append the properties to the URL like this:
  2. connectionProperties - A list of driver specific properties passed to the driver for creating connections. Each property is given as name=value, multiple properties are separated by semicolons. Default: no properties
Other interesting driver connection properties
  • queryTimeout - set default value for Statement.setQueryTimeout()
  • sduSize - the maximum packet size the driver is sending. default is 2920
  • failover - if set on true, the driver connects to alternative hosts, if connecting failed, default is false
  • loadbalance - if set on true, driver connects to one of the given hosts, default is "failover" value
  • initSQL - This query is executed once per connection

No comments:

Post a Comment