Identity Reporting reports fail with error ORA-01799: a column may not be outer-joined to a subquery

  • 7023326
  • 04-Sep-2018
  • 04-Sep-2018

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,131 [INFO] OsgiBridge [RPT-CORE] Running report 'Identity_Vault_User_Report'.
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.

Feedback service temporarily unavailable. For content questions or problems, please contact Support.