I have been receiving an intermittent ORA-00904 error, org.hibernate.exception.SQLGrammarException: could not load an entity. This query runs hundreds of thousands of times a day and fails maybe 100 times. It will succeed if tried again with the same data, so it is not a data related issue. It only fails in our production environment, the error can not be reproduced in dev or qa. The difference between the environments is that production runs with four jvms and there are some other more subtle difference, but same version of hibernate, database, everything important.

I have included the stack trace and the named query. The other thing that I have noted is that when I have show-sql on hibernate does not put any aliases in front of any of the columns, but in the failed call it has aliased the column. Not sure whether that is indication of the problem or not.

I would appreciate any help I could get. I have been looking at this problem for a while and have not gotten anywhere.

Here is the named query from my SQLQueries.hbm.xml file. Also, the cache-region for the query is different than the cache region for the hibernate object. This is the only change that I made since our last deploy and it is only since our last deploy that we have been having this problem. I would redeploy with the cache region changed back, but I need more proof that is the problem. it seems like the object and the query shouldn't need to occupy the same cache region.

Thanks,

Sharon

<sql-query
name="MemberContractImpl.findContractsByMemberIdAndTypes"
cacheable="true" cache-region="FindContractsCache">
<![CDATA[
select contractDTO.*, NVL((SELECT 'Y' monthly_manual
from xxx.xxx_okc_headers foh
WHERE NVL (foh.grandfathered, 'N') = 'Y'
AND (SELECT COUNT (1)
FROM xxxoks_bank_accounts_all fba
WHERE fba.chr_id = foh.contract_id) = 0
AND foh.contract_number = contractDTO.contract_number), 'N') manual_monthly,
(SELECT COMPANY
FROM apps.xxx_mi_members_v fmv
WHERE fmv.MEMBER_NUMBER = UPPER(:memberId)) COMPANY
from apps.XXX_MSO_AGREEMENTS_V contractDTO
where contractDTO.MEMBER_NUMBER = UPPER(:memberId)
and contractDTO.record_type in(:recordType)

]]>
<return alias="contractDTO"
class="com.location.men.contract.MemberContractImpl" />
</sql-query>

Here is the stack trace:
org.hibernate.exception.SQLGrammarException: could not load an entity: [com.location.men.contract.MemberContractImpl#MEMCONT01]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1799)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:47)
at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:41)
at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:2730)
at org.hibernate.event.def.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:365)
at org.hibernate.event.def.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:346)
at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:123)
at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:177)
at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:87)
at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:862)
at org.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:830)
at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:266)
at org.hibernate.type.ManyToOneType.assemble(ManyToOneType.java:177)
at org.hibernate.cache.StandardQueryCache.get(StandardQueryCache.java:121)
at org.hibernate.loader.Loader.getResultFromQueryCache(Loader.java:2086)
at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2051)
at org.hibernate.loader.Loader.list(Loader.java:2021)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1655)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at com.location.framework.storage.genericdao.impl.GenericDaoHibernateImpl.executeFinder(GenericDaoHibernateImpl.java:140)
at com.location.framework.storage.genericdao.finder.impl.FinderIntroductionInterceptor.invoke(FinderIntroductionInterceptor.java:31)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy285.findcontractsByMemberNumberAndTypes(Unknown Source)
at com.location.men.contract.XXContractFinderImpl.findContractsByMemberIdAndTypes(XXContractFinderImpl.java:184)
at com.location.men.contract.ContractFinderImpl.findContractsByMemberIdAndTypes(ContractFinderImpl.java:70)
at com.location.men.contract.ContractManagerImpl.findContracts(contractManagerImpl.java:139)
at com.location.men.contract.ContractManagerImpl.retrieveFirstContract(ContractManagerImpl.java:602)
at sun.reflect.GeneratedMethodAccessor311.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:198)
at $Proxy296.retrieveBaseMembershipcontract(Unknown Source)
at com.location.men.contract.contractServiceImpl.retrieveBaseMembershipcontract(contractServiceImpl.java:213)
at sun.reflect.GeneratedMethodAccessor319.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at com.location.architecture.instrumentation.interceptor.DataCollectorInterceptor.invoke(DataCollectorInterceptor.java:129)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy304.retrieveBaseMembershipcontract(Unknown Source)
at sun.reflect.GeneratedMethodAccessor319.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.remoting.support.RemoteInvocationTraceInterceptor.invoke(RemoteInvocationTraceInterceptor.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy304.retrieveBaseMembershipcontract(Unknown Source)
at sun.reflect.GeneratedMethodAccessor2486.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.remoting.support.RemoteInvocation.invoke(RemoteInvocation.java:205)
at org.springframework.remoting.support.DefaultRemoteInvocationExecutor.invoke(DefaultRemoteInvocationExecutor.java:38)
at org.springframework.remoting.support.RemoteInvocationBasedExporter.invoke(RemoteInvocationBasedExporter.java:76)
at org.springframework.remoting.support.RemoteInvocationBasedExporter.invokeAndCreateResult(RemoteInvocationBasedExporter.java:112)
at org.springframework.remoting.httpinvoker.HttpInvokerServiceExporter.handleRequest(HttpInvokerServiceExporter.java:117)
at org.springframework.web.servlet.mvc.HttpRequestHandlerAdapter.handle(HttpRequestHandlerAdapter.java:49)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:859)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:793)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:476)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:441)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:230)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:179)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:84)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:104)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:543)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.cata
2012-09-06 14:35:22,446 1605547723 INFO [t.com%2F10.0.9.22-8280-15] impl.Log4JLogger ( Log4JLogger.java: 205) - lina.connector.CoyoteAdapter.service(CoyoteAdapter.java:241)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:580)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: ORA-00904: "MEMBERCON0_"."COMPANY": invalid identifier

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:799)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:839)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1132)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:90)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
... 90 more