Free Download Spring in Action PDF

You can download Spring in action from below link:
Spring in Action

ORA-12899: value too large for column, Could not synchronize database state with session

I was getting below error when I was trying to insert the values in table:

[CAR] [2010-02-17 10:05:50,301] WARN [http-80-6] JDBCExceptionReporter.logExceptions(77) SQL Error: 12899, SQLState: 72000
[CAR] [2010-02-17 10:05:50,302] ERROR [http-80-6] JDBCExceptionReporter.logExceptions(78) ORA-12899: value too large for column "CARS"."CAR_ATTRIBUTE"."ATTR_VALUE" (actual: 2199, maximum: 2000)
[CAR] [2010-02-17 10:05:50,305] ERROR [http-80-6] AbstractFlushingEventListener.performExecutions(301) Could not synchronize database state session
org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:142)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:558) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:662)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:632) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:319)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:116)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy28.getAttributeValueProcessStatus(Unknown Source)
at com.belk.car.app.webapp.controller.DashBoardFormController.formBackingObject(DashBoardFormController.java:327)
.
.
.
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:735)
Caused by:
java.sql.BatchUpdateException: ORA-12899: value too large for column "CARS"."CAR_ATTRIBUTE"."ATTR_VALUE" (actual: 2199, maximum: 2000)
at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:602)
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9350)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:210)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297) at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
... 91 more


CAUSE:
Error clearly indicates that attribute value is larger than expected.
I was getting this error because the attribute I was trying to insert has value larger than the table attribute size.
In my case, the actual attribte value size was varchar(2000) however I was trying to insert more than that approx (2199)

Solution:
There could be two possible ways with which you can go with:
1. Update the table to increase the column size (In above case it could be varchar(2500)).
However, I dont suggest above option because in to change the table structure after insterting data is really very bad idea.
2. Change the attribute value which you are trying to insert and put proper size check and exception handeling.

The ultimate solution for such type of problem is prevention.
The table structure should be defined as per the requirement in the modeling phase of project.

ORA-01795: maximum number of expressions in a list is 1000

I got Following error while fetching data from Oracle 10g using hibernate:

[CAR] [2010-02-14 23:59:02,994] WARN [QuartzScheduler_Worker-4] JDBCExceptionReporter.logExceptions(77) | SQL Error: 1795, SQLState: 42000
[CAR] [2010-02-14 23:59:02,994] ERROR [QuartzScheduler_Worker-4] JDBCExceptionReporter.logExceptions(78) | ORA-01795: maximum number of expressions
in a list is 1000

[CAR] [2010-02-14 23:59:02,996] ERROR [QuartzScheduler_Worker-4] TestManagerImpl.processFile(61) | - reading XML: failure
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarExce
ption: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:613)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:378)
at at java.lang.reflect.Method.invoke(Method.java:612)
at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:283)
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactory
Bean.java:272)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
at org.quartz.core.JobRunShell.run(JobRunShell.java:195)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by:
java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1202)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 39 more

Workaround:

- When working with Oracle database, ORA-01795 (maximum number of expressions in a list) is a typical error when working with a large number of expressions in list parameters (IN(expr_list)).
- The workaround would be to use a sub-query
- eg:
select * from employee where emp_id in (1,2,3, ... 3000)
partition this parameter list in smaller lists with a maximum of 1000 elements (the oracle limit)