AUD: Project Configuration Errors with ORA-30563: Outer Join Operator (+) Is Not Allowed Here (Doc ID 2014274.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Project Foundation - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

After upgrading the database to 12c (12.1.0.2), the AUD: Project Configuration report errors with ORA-30563: outer join operator (+) is not allowed here. From the log file:

------------
P_PROJECT_ID='32'
------------

Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
American_America.US7ASCII

'.,'

@ Enter Password:
ORA-30563: outer join operator (+) is not allowed here
 WHERE ( p.person_id = per.person_id (+) and p.override_from_organization_id
= org1.organization_id (+) and org1.organization_id = org1tl.organization_id
(+) and decode ( org1tl.organization_id , null , '1' , org1tl.language ) =
decode ( org1tl.orga
REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error:
REP-300: outer join operator (+) is not allowed here
 WHERE ( p.person_id = per.person_id (+) and p.override_from_organization_id
= org1.organization_id (+) and org1.organization_id = org1tl.organization_id
(+) and decode ( org1tl.organization_id , null , '1' , org1tl.language ) =
decode ( org1tl.orga

The customer has identified the issue as follows:

The issue is in the 'q_cost_dist_or' query. The query is the following:

SELECT
  p.project_id,
per.full_name Employee_Name,
substr(org1tl.name,1,60) Source_Organization, /* Bug No. 2487147, UTF8
change: used substr for name */
p.expenditure_category,
substr(org2.name,1,60) Destination_Organization, /*Bug No. 2487147, UTF8
change: used substr for name */
p.start_date_active Start_Date,
p.end_date_active End_Date
FROM
  hr_org_units_no_join org1,
  hr_all_organization_units_tl org1tl,
  hr_organization_units org2,
pa_cost_dist_overrides p,
-- per_people_f per
  pa_employees per
WHERE
  p.person_id = per.person_id(+)
and p.override_from_organization_id = org1.organization_id (+)
and org1.organization_id = org1tl.organization_id (+)
and decode(org1tl.organization_id,null,'1',org1tl.language)=
  decode(org1tl.organization_id,null,'1',userenv('lang'))
and p.override_to_organization_id = org2.organization_id
/*and sysdate between per.effective_start_date(+) and
per.effective_end_date(+)
and per.employee_number (+) IS NOT NULL*/
ORDER BY per.last_name(+)
, per.first_name(+)
, org1tl.name
, p.expenditure_category
, org2.name;

If you notice the order by has (+) symbols indicating an outer join.

Here is the error message quote from the 12c Release 1 Database Administration Database Error Messages:

"ORA-30563: outer join operator (+) is not allowed here
Cause: An attempt was made to reference (+) in either the select-list, CONNECT BY clause, START WITH clause, or ORDER BY clause.
Action: Do not use the operator in the select-list, CONNECT BY clause, START WITH clause, or ORDER BY clause.

It is clear the 12c database no longer supports the order by clause having an outer join. From what I can find this function was depreciated in about version 8 of the database. As we were running this report as part of our
RPC2 patching along with an upgrade of the database from 11.2.0.4 to 12.1.0.2, the report will no longer correctly run on a 12c database.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms