Any clue for solving "ORA-01795: maximum number of expressions in a list is 1000" ?

[David Walend]

I'm getting a nasty, cryptic message from a remote site that's using Oracle. Anyone have an idea of how to work around this limit from Oracle? The likely problem is that "inSet" 

The code looks like this:

  def selectMostRecentQepQueryFlagsFor(networkIds:Set[NetworkQueryId]):Map[NetworkQueryId,QepQueryFlag] = {

    val flags:Seq[QepQueryFlag] = dbRun(mostRecentQueryFlags.filter(_.networkId inSet networkIds).result)

    flags.map(x => x.networkQueryId -> x).toMap

  }

My hope is that Oracle has a way to make that expression limit of 1000 be something larger. 

The grim stack trace is:

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

 

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)

        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)

        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)

        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)

        at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)

        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)

        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)

        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)

        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)

        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)

        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)

        at slick.jdbc.StatementInvoker.results(StatementInvoker.scala:39)

        at slick.jdbc.StatementInvoker.iteratorTo(StatementInvoker.scala:22)

        at slick.jdbc.Invoker$class.foreach(Invoker.scala:48)

        at slick.jdbc.StatementInvoker.foreach(StatementInvoker.scala:16)

        at slick.jdbc.StreamingInvokerAction$class.run(StreamingInvokerAction.scala:22)

        at slick.driver.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1.run(JdbcActionComponent.scala:218)

        at slick.driver.JdbcActionComponent$QueryActionExtensionMethodsImpl$$anon$1.run(JdbcActionComponent.scala:218)

        at slick.backend.DatabaseComponent$DatabaseDef$$anon$2.liftedTree1$1(DatabaseComponent.scala:237)

        at slick.backend.DatabaseComponent$DatabaseDef$$anon$2.run(DatabaseComponent.scala:237)

        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

        at java.lang.Thread.run(Thread.java:619)

Thanks,

David 

[David Walend]

https://community.oracle.com/thread/235143?start=0 has a long list of possible work-arounds. 

I think a fix based on one of these would have to be done in Slick's oracle driver. Does the crew at Light Bend want a bug report?

Thanks,

David