-
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
-
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:
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.