Document Types not listed when assigning document type restriction to a Documents and Media folder when using Oracle database

• Type: Bug
• Status: Closed
• Resolution: Fixed
• Affects Version/s: 7.0.0 M3
• Fix Version/s: 6.2.2 CE GA3 , 6.2.3 CE GA4, 6.2.X EE, 7.0.0 M2
• Component/s: Documents & Media
• Labels:
  • liferay-fixpack-document-management-14-6210
  • liferay-fixpack-document-management-17-6210
• Branch Version/s:
  6.2.x
• Backported to Branch:
  Committed
• Story Points: 9
• Fix Priority: 3
• Databases:
  Oracle Database 11.2
• Git Pull Request: https://github.com/brianchandotcom/liferay-portal/pull/21553

Description
Document Types not listed when assigning document type restriction to a Documents and Media folder when using Oracle database

Steps to reproduce/Testing done
With an Oracle database and hotfix-2481 (or SP8):

1. Go to Admin -> Site Administration -> Content
2. Click Documents and Media
3. Choose: Add -> Folder
4. Enter a name for the folder and click Save
5. Find the folder in the folder list and choose Edit from the actions menu
6. Select "Define specific document type restrictions and workflow for this folder."
7. Click the "Select Document Type" button

No document types are listed when using Oracle. Using MySQL shows a paginated listing of all available document types.

Additionally, attempting to search for a Document Type does return appropriate results.

Results of Testing
Expected behavior: A paginated listing of all document types appears
Actual behavior: No document types are listed

Errors/Logs

23:13:31,883 ERROR [http-bio-8080-exec-9][JDBCExceptionReporter:82] ORA-00907: missing right parenthesis_ [Sanitized]
23:13:32,125 ERROR [http-bio-8080-exec-9][IncludeTag:129] Current URL /group/control_panel/manage?p_p_auth=j2xFc0ck&p_p_id=20&p_p_lifecycle=0&p_p_state=pop_up&p_p_mode=view&doAsGroupId=10182&refererPlid=10185&controlPanelCategory=current_site.content&_20_includeBasicFileEntryType=1&_20_struts_action=%2Fdocument_library%2Fselect_restricted_file_entry_type generates exception: An exception occurred processing JSP page /html/portlet/document_library/file_entry_type_search_results.jspf at line 26____23: __24: searchContainer.setTotal(total);__25: __26: results = DLFileEntryTypeServiceUtil.search(company.getCompanyId(), PortalUtil.getSiteAndCompanyGroupIds(themeDisplay), searchTerms.getKeywords(), includeBasicFileEntryType, searchContainer.getStart(), searchContainer.getEnd(), searchContainer.getOrderByComparator());__27: __28: searchContainer.setResults(results);__29: %>______Stacktrace: [Sanitized]
java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis_ [Sanitized]
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
	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:14:30,374 ERROR [http-bio-8080-exec-10][JDBCExceptionReporter:82] ORA-00907: missing right parenthesis_ [Sanitized]
23:14:30,380 ERROR [http-bio-8080-exec-10][IncludeTag:129] Current URL /group/control_panel/manage?p_p_auth=rVJWwRN6&p_p_id=20&p_p_lifecycle=0&p_p_state=pop_up&p_p_mode=view&doAsGroupId=10182&refererPlid=10185&controlPanelCategory=current_site.content&_20_includeBasicFileEntryType=1&_20_struts_action=%2Fdocument_library%2Fselect_restricted_file_entry_type 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-00907: missing right parenthesis_ [Sanitized]
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
	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)

Reproduced on master: 27c94fe20d9bd146e99cec17363f242fab6b45b3