Some exceptions in Oracle

Gotchas With Oracle Jdbc Driver


          1－ java.sql.SQLException: ソケットから読み込むデータはこれ以上ありません（java.sql.SQLException: No more data to read from socket）
           Driver Version: 9i.*    or 10g.*

           该异常通常是因为使用了连接池，当从连接池取得的connection失效或者超时的时候，使用这个连接来进行数据库操作就会抛出以上异常。
           解决方法就是让数据库连接池在给你返回connection之前，检查该connnection是否超时或者失效，如果是，则evict这个connection，并返回一个可用的connection。
           以DBCP为例，做如下配置即可解决问题：
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
  <property name="url">
   <value>＄{jdbc.url}</value>
  </property>
  <property name="driverClassName">
   <value>＄{jdbc.driver}</value>
  </property>
  <property name="username">
   <value>＄{jdbc.username}</value>
  </property>
  <property name="password">
   <value>＄{jdbc.password}</value>
  </property>
  <property name="testOnBorrow">
   <value>true</value>
  </property>
  <property name="testOnReturn">
   <value>true</value>
  </property>
  <property name="testWhileIdle">
   <value>true</value>
  </property>
  <property name="minEvictableIdleTimeMillis">
   <value>180000</value>
  </property>
  <property name="timeBetweenEvictionRunsMillis">
   <value>360000</value>
  </property>
  <property name="validationQuery">
   <value>SELECT 1 FROM SYS.DUAL</value>
  </property>
  <property name="maxActive">
   <value>100</value>
  </property>
</bean>
          另外，你也可以参考这里：http://www.websina.com/bugzero/errors/oracle-SQLException.html
          转贴： Oracle SQLException: No more data to read from socket
java.sql.SQLException: No more data to read from socket at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963)
at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:375)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1094)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2132)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2015)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2877)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)

This error most likely occurs in applications that use a database connections pool. When the application checked out a connection that has been timed out or has been staled, and used it to connect to the database, this error occurs.
You may need start your Oracle database server as well as your Java application. In a better designed system, however, the staled connection should be cleared out and a new connection should be establised automatically.
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
          2－ORA-17004: Invalid column type  （java.sql.SQLException: 列の型が無効です。）         Driver Version: 9i.*

          该异常初次出现在使用spring+iBatis的程序中，后来通过检查出现错误的字段和SQLMAP的参考文档，才发现问题之所在。
          如果你使用iBatis，那从他的SqlMap参考文档中应该找到以下文字，当你看到他们的时候，你就发现了通向成功之门的钥匙，呵呵
          Note! Most drivers only need the type specified for nullable columns.  Therefore, for such drivers you only
need to specify the type for the columns that are nullable. 
          Note! When using an Oracle driver, you will get an “Invalid column type” error if you attempt to set a null
value to a column without specifying its type. 
          也就是说，当某个column允许为空，而你传的参数对应该column的值也为null的时候，对于oracle的驱动来说，这个异常是铁定的了。

          解决方法，可以通过iBatis的parameterMap，指定parameter元素的jdbcType和nullValue来解决；如果你没有使用iBatis，那你可以通过检查参数，如果他对应的列为可以为空，而当前值恰好就是空的时候，为他设置一个不是空的值即可。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
           3－java.sql.SQLException: OALL8矛盾した状態にあります;（java.sql.SQLException: OALL8 is in an inconsistent state.）

           该异常在我们的程序中通常是在第一个异常出现之后出现，但也不尽然，该异常搜遍网上也找不到合理的解释，只有以下信息可能会有用一些（from   http://forums.oracle.com/forums/thread.jspa?messageID=1275383）：
This is known to occur under when you are using too big an array size. How big your array can be depends on the length of each record and the Driver/Database combination. If you exceed the maximum size you will get the "OALL8" SQLException and your connection object may become unusable.

This message is also created if you are using the following:

9.0.1 Database
10.1.0 JDBC Driver
Generated Code that passes in an ARRAY or VARRAY of VARCHAR2 as a Parameter

            或者（from http://opensource.atlassian.com/projects/spring/browse/SPR-1545?decorator=none&view=rss）：
[SPR-1545] Oracle error 17447 should result in a DataAccessResourceFailureException
Oracle error 17447 is currently an unmapped exception but it should be a DataAccessResourceFailureException.  This error occurs when a JDBC connection has become corrupted, usually because of failure to properly close a connection before returning the connection to the connection pool.  Here is the error message:

SQL state [null]; error code [17447]; OALL8 is in an inconsistent state; nested exception is java.sql.SQLException: OALL8 is in an inconsistent state
    java.sql.SQLException: OALL8 is in an inconsistent state

This is a kind of "oh crap, something bad happened and it's not really your fault" exception in Oracle.  A few causes of this message are suggested in my searches on Google, including (see http://forums.oracle.com/forums/thread.jspa?threadID=274018&tstart=0):
- use of Oracle 10g JDBC drivers to connect to Oracle 9 databases
- using too big an array size (9.0.1 Database, 10.1.0 JDBC Driver and Generated Code that passes in an ARRAY or VARRAY of VARCHAR2 as a Parameter )

In any case, after this exception is thrown the connection is corrupted and unusable, hence why I advocate this exception be mapped to a DataAccessResourceFailureException

            解决方法，我也不知道，呵呵，反正调整了第一个问题之后，这个异常再没有在我们的程序中出现。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－