java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

Atlassian JIRA | Der Lun Ooi | 4 months ago
  1. 0

    Steps to replicate # Install Confluence 5.8.18 or 5.10.3 with Oracle 12c # Login to Confluence # Create a new Space # Create a new page # Generate 1001 dummy files # Drag all 1001 files and drop it to the newly created page # Save the page # Click on one of the files to preview after saving and you will see the following error in the logs : {code}Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:379) at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89) at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880) at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273) at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138) at net.sf.hibernate.loader.Loader.doList(Loader.java:1063) at net.sf.hibernate.loader.Loader.list(Loader.java:1048) at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1570) at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49) at com.atlassian.hibernate.HibernateObjectDao.lambda$findNamedQueryStringParams$5(HibernateObjectDao.java:432) at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:370) ... 324 more{code} *Current behavior* : an Oracle error is thrown if more than 1000 attachments is being attached to a page. *Expected behavior* : Oracle error should not be thrown when more than 1000 attachment is being attached.

    Atlassian JIRA | 4 months ago | Der Lun Ooi
    java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
  2. 0

    Error was thrown where Confluence (with Oracle Database) is trying to export a large space. {code} 2015-12-08 14:35:49,592 INFO [Long running task: Export Space] [atlassian.confluence.upgrade.AbstractUpgradeManager] getExportBuildNumber Build number 4937: "Reindexes all PersonalInformation objects, creating them for any users that do not have one." - Ok 2015-12-08 14:35:49,592 INFO [Long running task: Export Space] [atlassian.confluence.upgrade.AbstractUpgradeManager] getExportBuildNumber Build number 4526: "Remove the legacy remotable apps plugin from the user installed plugins" - Ok 2015-12-08 14:35:49,593 INFO [Long running task: Export Space] [atlassian.confluence.upgrade.AbstractUpgradeManager] getExportBuildNumber Build number 4517: "Migrate all Page Templates using a storage format round-trip conversion" - Not Ok 2015-12-08 14:35:49,593 INFO [Long running task: Export Space] [atlassian.confluence.upgrade.AbstractUpgradeManager] getExportBuildNumber Backward compatibility for export: "Migrate all Page Templates using a storage format round-trip conversion" prevents compatibility before 4517 2015-12-08 14:36:48,836 ERROR [Long running task: Export Space] [sf.hibernate.util.JDBCExceptionReporter] logExceptions ORA-01795: maximum number of expressions in a list is 1000 -- referer: https://www-test.wiki.ed.ac.uk/spaces/exportspacexml.action?key=GenePool | url: /spaces/doexportspace.action | userName: aforres3 | action: doexportspace 2015-12-08 14:36:48,837 ERROR [Long running task: Export Space] [sf.hibernate.util.JDBCExceptionReporter] logExceptions ORA-01795: maximum number of expressions in a list is 1000 -- referer: https://www-test.wiki.ed.ac.uk/spaces/exportspacexml.action?key=GenePool | url: /spaces/doexportspace.action | userName: aforres3 | action: doexportspace 2015-12-08 14:36:48,837 ERROR [Long running task: Export Space] [confluence.importexport.impl.AbstractXmlExporter] backupEntities Couldn't backup database data. -- referer: https://www-test.wiki.ed.ac.uk/spaces/exportspacexml.action?key=GenePool | url: /spaces/doexportspace.action | userName: aforres3 | action: doexportspace org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.orm.hibernate.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:364) at org.springframework.orm.hibernate.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:351) at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:375) at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:337) at com.atlassian.confluence.like.HibernateLikeEntityDao.getLikeEntities(HibernateLikeEntityDao.java:138) at com.atlassian.confluence.importexport.impl.XmlExporterObjectHandleCollector.getLikesFromContent(XmlExporterObjectHandleCollector.java:174) at com.atlassian.confluence.importexport.impl.XmlExporterObjectHandleCollector.getContentAndContentPropertiesFromSpace(XmlExporterObjectHandleCollector.java:136) at com.atlassian.confluence.importexport.impl.XmlExporterObjectHandleCollector.addEntityHandlesFromSpace(XmlExporterObjectHandleCollector.java:118) at com.atlassian.confluence.importexport.impl.XmlExporterObjectHandleCollector.getObjectHandles(XmlExporterObjectHandleCollector.java:94) at com.atlassian.confluence.importexport.impl.XmlExporterObjectHandleCollector.getHandlesOfObjectsForExport(XmlExporterObjectHandleCollector.java:82) at com.atlassian.confluence.importexport.impl.XmlExporter.getHandlesOfObjectsForExport(XmlExporter.java:118) at com.atlassian.confluence.importexport.impl.AbstractXmlExporter.backupEntities(AbstractXmlExporter.java:218) at com.atlassian.confluence.importexport.impl.AbstractXmlExporter.backupEverything(AbstractXmlExporter.java:104) at com.atlassian.confluence.importexport.impl.FileXmlExporter.backupEverything(FileXmlExporter.java:96) at com.atlassian.confluence.importexport.impl.AbstractXmlExporter.doExport(AbstractXmlExporter.java:97) at com.atlassian.confluence.importexport.impl.FileXmlExporter.doExport(FileXmlExporter.java:54) at com.atlassian.confluence.importexport.impl.XmlExporter.doExport(XmlExporter.java:51) at com.atlassian.confluence.importexport.DefaultImportExportManager.exportAs(DefaultImportExportManager.java:122) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149) 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 com.sun.proxy.$Proxy121.exportAs(Unknown Source) at com.atlassian.confluence.importexport.actions.ExportSpaceLongRunningTask$1.doInTransactionWithoutResult(ExportSpaceLongRunningTask.java:163) at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:33) at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:128) at com.atlassian.confluence.importexport.actions.ExportSpaceLongRunningTask.runInternal(ExportSpaceLongRunningTask.java:124) at com.atlassian.confluence.util.longrunning.ConfluenceAbstractLongRunningTask.run(ConfluenceAbstractLongRunningTask.java:27) at com.atlassian.confluence.util.longrunning.ManagedTask.run(ManagedTask.java:58) at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89) at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880) at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273) at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138) at net.sf.hibernate.loader.Loader.doList(Loader.java:1063) at net.sf.hibernate.loader.Loader.list(Loader.java:1048) at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854) at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1570) at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49) at com.atlassian.confluence.like.HibernateLikeEntityDao$6.doInHibernate(HibernateLikeEntityDao.java:147) at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:370) ... 37 more {code} Seems like oracle is limiting the expression to 1000 which causing the export to fail. !exportfaileddd.png|thumbnail!

    Atlassian JIRA | 11 months ago | Jonathan Soo [Atlassian]
    org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
  3. 0

    Hibernate says invalid identifier even if column is available in db

    Stack Overflow | 2 years ago | sr.praneeth
    java.sql.SQLSyntaxErrorException: ORA-00904: "TABLETYPE0_"."TTPNAME": invalid identifier
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    SQLGrammarException with Hibernated and oracle11g

    Stack Overflow | 2 years ago | maximegir
    java.sql.SQLSyntaxErrorException: ORA-02289: la séquence n'existe pas (non existing sequence)
Not finding the right solution?
Take a tour to get the most out of Samebug.

Tired of useless tips?

Automated exception search integrated into your IDE

Root Cause Analysis

  1. java.sql.SQLSyntaxErrorException

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

    at oracle.jdbc.driver.T4CTTIoer.processError()
  2. Oracle jdbc
    OraclePreparedStatementWrapper.executeQuery
    1. oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    2. oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    3. oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    4. oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    5. oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    6. oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    7. oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
    8. oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
    9. oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
    10. oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
    11. oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
    12. oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
    13. oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
    14. oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
    14 frames
  3. c3p0:JDBC DataSources/Resource Pools
    NewProxyPreparedStatement.executeQuery
    1. com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:379)
    1 frame
  4. net.sf.hibernate
    QueryImpl.list
    1. net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
    2. net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
    3. net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
    4. net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
    5. net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
    6. net.sf.hibernate.loader.Loader.list(Loader.java:1048)
    7. net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
    8. net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1570)
    9. net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
    9 frames
  5. com.atlassian.hibernate
    HibernateObjectDao.lambda$findNamedQueryStringParams$5
    1. com.atlassian.hibernate.HibernateObjectDao.lambda$findNamedQueryStringParams$5(HibernateObjectDao.java:432)
    1 frame
  6. Hibernate
    HibernateTemplate.execute
    1. org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:370)
    1 frame