Oracle 17070:数据大小超出此类型的最大值

本人在使用oracle驱动的时候总出现SQL异常,经多次验证是因为同时使用了下面定义的四种方式后出现。

后来我改用Oracle的OCI驱动,该异常就不再出现。

CHAR and VARCHAR2 Data Size Restriction With the Thin Driver

If the database character set is neither ASCII (US7ASCII) nor ISO-LATIN-1 (WE8ISO8859P1), then the Thin driver must impose size restrictions for CHAR and VARCHAR2 bind parameters that are more restrictive than normal database size limitations. This is necessary to allow for data expansion during conversion.

The Thin driver checks CHAR or VARCHAR2 bind sizes when the setXXX() method is called. If the data size exceeds the size restriction, then the driver throws a SQL exception (ORA-17070 "Data size bigger than max size for this type") from the setXXX() call. This limitation is necessary to avoid the chance of data corruption whenever an NLS conversion occurs and increases the length of the data. This limitation is enforced when you are doing all the following:

  • Using the Thin driver

     

  • Using binds (not defines)

     

  • Using CHAR or VARCHAR2 datatypes

     

  • Connecting to a database whose character set is neither ASCII (US7ASCII) nor ISO-Latin-1 (WE8ISO8859P1)
展开阅读全文

数据大小出此类型最大值?

07-08

我有一字段,类型是VARCHAR2(4000)的,通过PreparedStatement的setString方法设置值,发现有如下的问题:rnrn采用jdbc:oracle:thin,当汉字个数大约为690个左右时,就会报“数据大小超出此类型的最大值”的错误;如果是英文字符或数字,则刚好2000个,再大就会报错。采用汉字时,我用getBytes().length来统计字节长度,确发现只有1400个左右,但oracle确报超出2000的错误。不明白为什么会这样,是不是oracle的jdbc驱动中对字符的编码方式与我采用的不同?如果不同,则是按照什么来进行编码的?不知道其他数据库的jdbc驱动是否也有类似的情况?我知道目前有一个解决方法,就是采用setCharacterStream方法来,但我不清楚到底什么时候可以采用setString,什么时候可以采用setCharacterStream!rnrn采用jdbc:oracle:oci,则不会出现上面的情况。rn上面的问题在oracle 8.1.7和9i上都有。rnrn我查了oracle的官方文档,里面有下面一段描述:rnrnUsing Streams to Avoid Limits on setBytes() and setString()rnrnThere is a limit on the maximum size of the array which can be bound using the PreparedStatement class setBytes() method, and on the size of the string which can be bound using the setString() method.rnrnAbove the limits, which depend on the version of the server you use, you should use setBinaryStream() or setCharacterStream() instead.rnrnWhen connecting to an Oracle8 database, the limit for setBytes() is 2000 bytes (the maximum size of a RAW in Oracle8) and the limit for setString() is 4000 bytes (the maximum size of a VARCHAR2 in Oracle8).rnrnWhen connecting to an Oracle7 database, the limit for setBytes() is 255 bytes (the maximum size of a RAW in Oracle7) and the limit for setString() is 2000 bytes (the maximum size of a VARCHAR2 in Oracle7).rnrnThe 8.1.6 Oracle JDBC drivers may not raise an error if you exceed the limit when using setBytes() or setString(), but you may receive the following error:rnrnORA-17070: Data size bigger than max size for this typernrn我就更不明白了,不是说4000 bytes的限制么,怎么实际用起来只有2000,是我哪儿用错了?rnrn请教各位高手了!rn不胜感激! 论坛

Caused by: java.sql.SQLException: 数据大小出此类型最大值的问题?

09-24

在oracle9i数据库中,使用一个varchar类型的字段存储汉字,第一次保存的时候可以,但是在修改后再次保存的时候,报错,错误如下:rnCaused by: org.hibernate.exception.GenericJDBCException: could not update: [com.Chineseridge.hgyc.share.hibernate.Newsinf#402881211279f3a601127a0d2b920027]rn at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)rn at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)rn at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)rn at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:2000)rn at org.hibernate.persister.entity.BasicEntityPersister.updateOrInsert(BasicEntityPersister.java:1909)rn at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:2149)rn at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:75)rn at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)rn at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:223)rn at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:137)rn at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:274)rn at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)rn at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)rn at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:324)rn at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)rn at com.Chineseridge.hgyc.share.DAO.NewsinfDAO.update(NewsinfDAO.java:83)rn ... 29 morern[b]Caused by: java.sql.SQLException: 数据大小超出此类型的最大值: 2050rn at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)rn at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)rn at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:147)rn at oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.java:2460)rn at oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStatement.java:1190)rn at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:1610)rn at org.hibernate.type.StringType.set(StringType.java:24)rn at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:62)rn at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:39)rn at org.hibernate.persister.entity.BasicEntityPersister.dehydrate(BasicEntityPersister.java:1617)rn at org.hibernate.persister.entity.BasicEntityPersister.update(BasicEntityPersister.java:1963)rn ... 41 more[/b]rnrn我也在网上查找了一下相关的问题,有人说是oralce9的jdbc驱动的问题,说更新10g的驱动就可以了,可是更新后还是没有解决,问题依然存在,请高手不吝赐教...... 论坛

hiberate中提示数据大小出此类型最大值(急)

03-13

数据库为Oracle9i,用到hiberate连接数据库,后台抛错为:rn===================================rn03-13 07:02:22:324 THREAD:http-8088-Processor20 CLASS: at com.eshore.dakar.web.exception.strutshandler.DakarExceptionHandler.execute(DakarExceptionHandler.java:49) rnERROR: Hibernate operation: could not update: [com.eshore.dakar.database.entity.unibudget.UniBudgetArchive#8a91a6c71863e2750118640a6167029b]; uncategorized SQLException for SQL [update BIZ_OBJECT set NAME=?, DESCRIPTION=? where ID=?]; SQL state [null]; error code [17070]; 数据大小超出此类型的最大值: 2003; nested exception is java.sql.SQLException: 数据大小超出此类型的最大值: 2003rnorg.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not update: [com.eshore.dakar.database.entity.unibudget.UniBudgetArchive#8a91a6c71863e2750118640a6167029b]; uncategorized SQLException for SQL [update BIZ_OBJECT set NAME=?, DESCRIPTION=? where ID=?]; SQL state [null]; error code [17070]; 数据大小超出此类型的最大值: 2003; nested exception is java.sql.SQLException: 数据大小超出此类型的最大值: 2003rnjava.sql.SQLException: 数据大小超出此类型的最大值: 2003rn==================================rn目前对应数据的字段为varchar2(4000),用SQL语句insert,update都正常,为什么用hiberate保存和读取时都会错啊?rn是hiberate设置的不一样吗,还是JDBC的问题,请高手帮忙看一下。。。 论坛

Hibnerate报数据大小出此类型最大值错误

03-23

我的配置是spring1.2+hibernate3+strut1.2,数据库Oracle9 rn数据源在tomcat中,配置为: rn rn rn rn rn表的字段类型是varchar(4000);这里不能用clob,Blob(必要条件) rn我想存入1000个汉字,实际输入900个就报这个错误: rn- SQL Error: 17070, SQLState: null rn- 数据大小超出此类型的最大值: 2700 rn- Could not synchronize database state with session rn- [e] (HibernateTemplate): encountered SQLException [数据大小超出此类型的最大值: 2700]; nested exception is java.sql.SQLException: 数据大小超出此类型的最大值: 2700 rnorg.springframework.jdbc.UncategorizedSQLException: (HibernateTemplate): encountered SQLException [数据大小超出此类型的最大值: 2700]; nested exception is java.sql.SQLException: 数据大小超出此类型的最大值: 2700 rnjava.sql.SQLException: 数据大小超出此类型的最大值: 2700 rnat oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) rnat oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) rnat oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:147) rnat oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.java:2461) rnat oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStatement.java:1155) rnat oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:1572) rnat org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setString(DelegatingPreparedStatement.java:131) rnat net.sf.hibernate.type.StringType.set(StringType.java:26) rnat net.sf.hibernate.type.NullableType.nullSafeSet(NullableType.java:48) rnat net.sf.hibernate.type.NullableType.nullSafeSet(NullableType.java:35) rnat net.sf.hibernate.persister.EntityPersister.dehydrate(EntityPersister.java:387) rnat net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:460) rnat net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:436) rnat net.sf.hibernate.impl.ScheduledInsertion.execute(ScheduledInsertion.java:37) rnat net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2449) rnat net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2435) rnat net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2392) rnat net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2261) rnat net.sf.hibernate.transaction.JTATransaction.commit(JTATransaction.java:52) rnat org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:472) rnat org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:397) rnat org.springframework.transaction.interceptor.TransactionAspectSupport.doCommitTransactionAfterReturning(TransactionAspectSupport.java:243) rnat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:66) rnat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144) rnat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:174) rn我试了用了Oracle10的ojdbc14.jar放在tomcat\common\lib下,可是不行。那位达人能提供些解决的方案,在下不胜感激! 论坛

java.sql.SQLException: 数据大小出此类型最大值: 2253

12-04

java.sql.SQLException: 数据大小超出此类型的最大值: 2253rn at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)rn at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)rn at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:147)rn at oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.java:2461)rn at oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStatement.java:1155)rn at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:1572)rn at com.bjepn.news.NewsTypePO.prepareUpdateStatement(NewsTypePO.java:90)rn at ess.base.po.ESSTransaction.update(ESSTransaction.java:233)rn at com.bjepn.news.NewsTypeBO.updateNews(NewsTypeBO.java:59)rn at com.bjepn.news.NewsTypeTask.updateNewsType(NewsTypeTask.java:84)rn at com.bjepn.news.NewsTypeTask.execute(NewsTypeTask.java:19)rn at ess.base.task.ESSServlet.perform(ESSServlet.java:51)rn at ess.base.task.ESSServlet.doPost(ESSServlet.java:44)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)rn at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)rn at filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:107)rn at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)rn at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)rn at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)rn at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)rn at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)rn at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)rn at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)rn at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)rn at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:825)rn at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:743)rn at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)rn at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)rn at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)rn at java.lang.Thread.run(Thread.java:595)rnrnrn我用的是VARCHAR2(4000)的字段,急求解决方法~ 论坛

java.sql.SQLException: 数据大小出此类型最大值: 2077

03-15

java.sql.SQLException: 数据大小超出此类型的最大值: 2077rn at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)rn at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)rn at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:147)rn at oracle.jdbc.dbaccess.DBDataSetImpl.setBytesBindItem(DBDataSetImpl.java:2461)rn at oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStatement.java:1155)rn at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:1572)rn at net.sf.hibernate.type.StringType.set(StringType.java:26)rn at net.sf.hibernate.type.NullableType.nullSafeSet(NullableType.java:48)rn at net.sf.hibernate.type.NullableType.nullSafeSet(NullableType.java:35)rn at net.sf.hibernate.persister.EntityPersister.dehydrate(EntityPersister.java:394)rn at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:693)rn at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:668)rn at net.sf.hibernate.impl.ScheduledUpdate.execute(ScheduledUpdate.java:53)rn at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2303)rn at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2257)rn at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2182)rn at org.roller.business.persistence.hibernate.HibernateStrategy.release(HibernateStrategy.java:102)rn at org.roller.business.persistence.hibernate.HibernateStrategy.query(HibernateStrategy.java:430)rn at org.roller.business.persistence.hibernate.HibernateStrategy.query(HibernateStrategy.java:300)rn at org.roller.business.persistence.hibernate.HibernateStrategy.query(HibernateStrategy.java:262)rn at org.roller.busirn数据库是oracle 在执行查询语句是出现以上异常! 论坛

hibernate 写数据库时发生异常“ORACLE:数据大小出此类型最大值: 1024”

01-05

hibernate 写数据库时发生异常“ORACLE:数据大小超出此类型的最大值: 1024”rnrnrn在 oracle 9i上有这个问题 ,在oracle10g上就没有这个问题了。两个版本在这项限制上有区别吗?rnrn表中有两个字段定义为 varchar2(4000),存放的是固定长度为1024的字符串。rnrnjava.sql.SQLException: ORACLEata size bigger than max size for this type: 1024rn at org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter.reFactorException(OracleExceptionSorter.java:54)rn at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.refactorException(LocalManagedConnectionFactory.java:842)rn at org.jboss.resource.adapter.jdbc.local.LocalManagedConnection.refactorException(LocalManagedConnection.java:672)rn at org.jboss.resource.adapter.jdbc.local.LocalConnection.checkException(LocalConnection.java:919)rn at org.jboss.resource.adapter.jdbc.local.LocalStatement.checkException(LocalStatement.java:780)rn at org.jboss.resource.adapter.jdbc.local.LocalPreparedStatement.setString(LocalPreparedStatement.java:410)rn at org.hibernate.type.StringType.set(StringType.java:26)rn at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:131)rn at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:108)rn at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2002)rn at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2248)rn at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2665)rn at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:56)rn at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)rn at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)rn at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)rn at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)rn at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)rnrnrn中文环境下报错是 Caused by: java.sql.SQLException: ORACLE:数据大小超出此类型的最大值: 1024;rnrnrn网上有些资料表明是jdbc驱动的问题,但是我换了oracle 10.2.0.2 的驱动 文件Ojdbc14.jar(1.46 MB (1,540,457 字节)) 的也是无济于事,感觉还是数据库版本不同会有差别,oracle10就没有这个问题 。 论坛

没有更多推荐了,返回首页