AnsweredAssumed Answered

Alfresco Community running on JBOSS and Oracle 11g ORA-01795

Question asked by fracat71 on Apr 19, 2010
Latest reply on May 28, 2010 by fracat71
Hi all,
i'm using Alfresco Community running on JBOSS and Oracle 11g.
I have a space with 40000 subspces and wiht i try to access to it using Explorer i have this error:

17:01:43,628 ERROR [org.alfresco.web.ui.common.Utils] A system error happened during the operation: could not execute query
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2216)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.cacheNodesNoBatch(HibernateNodeDaoServiceImpl.java:3147)
at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.cacheNodes(HibernateNodeDaoServiceImpl.java:3103)
at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.cacheNodes(HibernateNodeDaoServiceImpl.java:3080)
at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.convertToChildAssocRefs(HibernateNodeDaoServiceImpl.java:3021)
at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.getChildAssocsByChildTypes(HibernateNodeDaoServiceImpl.java:2714)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
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.alfresco.repo.transaction.TransactionalDaoInterceptor.invoke(TransactionalDaoInterceptor.java:68)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.alfresco.repo.domain.hibernate.DirtySessionMethodInterceptor.invoke(DirtySessionMethodInterceptor.java:419)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.alfresco.repo.transaction.SingleEntryTransactionResourceInterceptor.invokeInternal(SingleEntryTransactionResourceInterceptor.java:163)
at org.alfresco.repo.transaction.SingleEntryTransactionResourceInterceptor.invoke(SingleEntryTransactionResourceInterceptor.java:138)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy65.getChildAssocsByChildTypes(Unknown Source)
at org.alfresco.repo.node.db.DbNodeServiceImpl.getChildAssocs(DbNodeServiceImpl.java:1666)
at sun.reflect.GeneratedMethodAccessor561.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
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.alfresco.repo.tenant.MultiTNodeServiceInterceptor.invoke(MultiTNodeServiceInterceptor.java:110)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy67.getChildAssocs(Unknown Source)
at sun.reflect.GeneratedMethodAccessor561.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.alfresco.repo.service.StoreRedirectorProxyFactory$RedirectorInvocationHandler.invoke(StoreRedirectorProxyFactory.java:221)
at $Proxy68.getChildAssocs(Unknown Source)
at sun.reflect.GeneratedMethodAccessor561.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
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.alfresco.repo.node.MLPropertyInterceptor.invoke(MLPropertyInterceptor.java:306)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.alfresco.repo.node.MLPropertyInterceptor.invoke(MLPropertyInterceptor.java:306)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.alfresco.repo.node.NodeRefPropertyMethodInterceptor.invoke(NodeRefPropertyMethodInterceptor.java:274)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.alfresco.repo.node.NodeRefPropertyMethodInterceptor.invoke(NodeRefPropertyMethodInterceptor.java:274)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy67.getChildAssocs(Unknown Source)
at org.alfresco.repo.model.filefolder.FileFolderServiceImpl.listSimple(FileFolderServiceImpl.java:509)
at org.alfresco.repo.model.filefolder.FileFolderServiceImpl.list(FileFolderServiceImpl.java:295)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
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.alfresco.repo.model.filefolder.TempFileMarkerInterceptor.invoke(TempFileMarkerInterceptor.java:83)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.alfresco.repo.model.ml.MLContentInterceptor.invoke(MLContentInterceptor.java:131)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.alfresco.repo.model.filefolder.MLTranslationInterceptor.invoke(MLTranslationInterceptor.java:211)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at net.sf.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:80)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.alfresco.repo.security.permissions.impl.ExceptionTranslatorMethodInterceptor.invoke(ExceptionTranslatorMethodInterceptor.java:49)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.alfresco.repo.audit.AuditMethodInterceptor.invoke(AuditMethodInterceptor.java:147)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy121.list(Unknown Source)
at org.alfresco.web.bean.BrowseBean.queryBrowseNodes(BrowseBean.java:905)
at org.alfresco.web.bean.BrowseBean.getNodes(BrowseBean.java:511)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.myfaces.el.PropertyResolverImpl.getProperty(PropertyResolverImpl.java:457)
at org.apache.myfaces.el.PropertyResolverImpl.getValue(PropertyResolverImpl.java:85)
at org.apache.myfaces.el.ELParserHelper$MyPropertySuffix.evaluate(ELParserHelper.java:535)
at org.apache.commons.el.ComplexValue.evaluate(ComplexValue.java:145)
at org.apache.myfaces.el.ValueBindingImpl.getValue(ValueBindingImpl.java:386)
at org.alfresco.web.ui.common.component.data.UIRichList.getValue(UIRichList.java:157)
at org.alfresco.web.ui.common.component.data.UIRichList.getDataModel(UIRichList.java:518)
at org.alfresco.web.ui.common.component.data.UIRichList.bind(UIRichList.java:454)
at org.alfresco.web.ui.common.renderer.data.RichListRenderer.encodeChildren(RichListRenderer.java:83)
at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:527)
at javax.faces.webapp.UIComponentTag.encodeChildren(UIComponentTag.java:483)
at javax.faces.webapp.UIComponentTag.doEndTag(UIComponentTag.java:364)
at org.apache.jsp.jsp.browse.browse_jsp._jspx_meth_a_005frichList_005f0(browse_jsp.java:2822)
at org.apache.jsp.jsp.browse.browse_jsp._jspx_meth_a_005fpanel_005f5(browse_jsp.java:2710)
at org.apache.jsp.jsp.browse.browse_jsp._jspService(browse_jsp.java:868)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:373)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:336)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
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.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:654)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:445)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:379)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:292)
at org.apache.myfaces.context.servlet.ServletExternalContextImpl.dispatch(ServletExternalContextImpl.java:419)
at org.apache.myfaces.application.jsp.JspViewHandlerImpl.renderView(JspViewHandlerImpl.java:211)
at org.apache.myfaces.lifecycle.RenderResponseExecutor.execute(RenderResponseExecutor.java:41)
at org.apache.myfaces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:132)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:140)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.alfresco.web.app.servlet.AuthenticationFilter.doFilter(AuthenticationFilter.java:110)
at sun.reflect.GeneratedMethodAccessor487.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.alfresco.repo.management.subsystems.ChainingSubsystemProxyFactory$1.invoke(ChainingSubsystemProxyFactory.java:122)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy244.doFilter(Unknown Source)
at org.alfresco.repo.web.filter.beans.BeanProxyFilter.doFilter(BeanProxyFilter.java:88)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.alfresco.repo.web.filter.beans.NullFilter.doFilter(NullFilter.java:74)
at sun.reflect.GeneratedMethodAccessor487.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.alfresco.repo.management.subsystems.ChainingSubsystemProxyFactory$1.invoke(ChainingSubsystemProxyFactory.java:122)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy244.doFilter(Unknown Source)
at org.alfresco.repo.web.filter.beans.BeanProxyFilter.doFilter(BeanProxyFilter.java:88)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
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:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000
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:213)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:796)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1031)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:836)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
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:2213)
… 161 more

This is due to an ORACLE IN Clause that is limited to 1000 elements.
This is an open issue ( https://issues.alfresco.com/jira/browse/ALF-2012?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel  ) on Alfresco JIRA i don't understand why is it presenet inside code?




##################################################################################################
#
# Alfresco version information
#
# Version label
version.major=3
version.minor=2
version.revision=0
version.label=r2
# Edition label
version.edition=Community
# Build number
version.build=2440
# Schema number
version.schema=3300
##################################################################################################
#
# JBOSS version information
#
JVM=1.6.0_18
##################################################################################################
#
# ORACLE DB version information
#
Version = 11g

Regards,
Francesco

Outcomes