Environment
Identity Manager 4.6.x
Identity Reporting 5.5.x
Oracle DB 12c (12.1.0.2.0)
Situation
Certain reports like 'Identity_Vault_User_Report' or 'Access_Requests_by_Requester' fail with the error:
ORA-01799: a column may not be outer-joined to a subquery
Resolution
This problem will occur if the parameter "_optimizer_ansi_rearchitecture" is set to FALSE.
With "SHOW
PARAMETERS;" it is possible to list all parameters for an Oracle Database Instance.
| DBFIPS_140 | boolean | FALSE | |||||
| O7_DICTIONARY_ACCESSIBILIT | boolean | FALSE | |||||
| _fix_control | string | 10216738:0 | |||||
| _gc_element_percent | integer | 200 | |||||
| _optimizer_aggr_groupby_elim | boolean | FALSE | |||||
| _optimizer_ansi_rearchitecture | boolean | FALSE | |||||
| _optimizer_reduce_groupby_key | boolean | FALSE | |||||
| _rowsets_enabled | boolean | FALSE | |||||
| _shutdown_completion_timeout_mins | integer | 30 | |||||
| _swrf_mmon_dbfus | boolean | FALSE | |||||
| _use_single_log_writer | string | true |
By default, this parameter is set to 'TRUE'. Setting "_optimizer_ansi_rearchitecture" back to its default value should solve the issue.
Additional Information
Example log output:
2018-02-02 09:49:31,176 [ERROR] OsgiBridge [RPT-CORE] The following error was encountered when attempting to run report 'Identity_Vault_User_Report': An error was detected while running report 'Identity_Vault_User_Report': Error executing SQL statement for : TemplateReport. ORA-01799: a column may not be outer-joined to a subquery
2018-02-02 09:49:31,176 [ERROR] ScheduleManagerImpl [RPT-CORE] The following error was encountered when attempting to run report 'Identity_Vault_User_Report 2018-02-02 09:49:29.290000': An error was detected while running report 'Identity_Vault_User_Report': Error executing SQL statement for : TemplateReport. ORA-01799: a column may not be outer-joined to a subquery
com.netiq.common.i18n.LocalizedException: The following error was encountered when attempting to run report 'Identity_Vault_User_Report 2018-02-02 09:49:29.290000': An error was detected while running report 'Identity_Vault_User_Report': Error executing SQL statement for : TemplateReport. ORA-01799: a column may not be outer-joined to a subquery
at com.novell.idm.rpt.core.server.schedules.ScheduleManagerImpl$ReportTask.runJasper(ScheduleManagerImpl.java:154)
at com.novell.idm.rpt.core.server.schedules.ScheduleManagerImpl$ReportTask.runAndUpdateReport(ScheduleManagerImpl.java:282)
at com.novell.idm.rpt.core.server.schedules.ScheduleManagerImpl$ReportTask.call(ScheduleManagerImpl.java:256)
at com.novell.idm.rpt.core.server.schedules.ScheduleManagerImpl$ReportTask.call(ScheduleManagerImpl.java:89)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2018-02-02 09:49:31,176 [ERROR] ScheduleManagerImpl [RPT-CORE] JasperReports execution failed.
2018-02-02 09:49:31,188 [INFO] ReportManagerImpl [RPT-CORE] Report is updated (finished) 3a1f618a-bfeb-4ae1-b7a0-a2b6b93fc164.