When a Site has more than 1000 users and Organizations as members, an Oracle SQLGrammarException error is thrown

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 6.1.X EE, 6.2.X EE, 7.0.0 M3
• Fix Version/s: 6.1.X EE, 6.2.3 CE GA4, 6.2.X EE, 7.0.0 M3
• Component/s: Core Infrastructure, Core Infrastructure > Database Access
• Labels:
  • liferay-fixpack-portal-38-6130
  • liferay-fixpack-portal-45-6210
• Branch Version/s:
  6.2.x, 6.1.x
• Backported to Branch:
  Committed
• Story Points: 14
• Fix Priority: 3
• Databases:
  Oracle Database 11.2
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/22451

When a Site has more than 1000 Organizations as members, an Oracle SQLGrammarException error is thrown

Steps
1. Start the portal using an Oracle database
2. Generate 1050 Organizations
3. Add all Organizations as a member of a site
4. Go to Control Panel > Sites

Errors

23:49:21,975 ERROR [http-bio-8080-exec-1][IncludeTag:129] Current URL /group/control_panel/manage?p_p_auth=Obwq2UmB&p_p_id=174&p_p_lifecycle=0&p_p_state=maximized&p_p_mode=view&doAsGroupId=10748&refererPlid=10757&controlPanelCategory=current_site.users&_174_struts_action=%2Fsite_memberships_admin%2Fedit_site_assignments&_174_tabs2=current&_174_redirect=&_174_showBackURL=true&_174_tabs1=users generates exception: com.liferay.portal.kernel.exception.SystemException: com.liferay.portal.kernel.dao.orm.ORMException: org.hibernate.exception.SQLGrammarException: could not execute query
java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000_ [Sanitized]
        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.executeQuery(OraclePreparedStatement.java:3628)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)
        at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
        at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
        at org.hibernate.loader.Loader.doQuery(Loader.java:802)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
        at org.hibernate.loader.Loader.doList(Loader.java:2542)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
23:57:22,830 ERROR [http-bio-8080-exec-7][JDBCExceptionReporter:82] ORA-01795: maximum number of expressions in a list is 1000_ [Sanitized]

Result
SQLSyntaxErrorException is thrown.
Expected
No SQL exceptions are thrown.

Notes:
This happens because Oracle does not allow more than 1000 parameters to be bound to an IN clause.