Invalid ROWID SQLs From PAY_GB_PEOPLE_SOE_V Causing Unnecessary but Heavy Hard Parsing "ORA-01446 error due to the EBS application running a query with ROWID on a VIEW"
(Doc ID 2887520.1)
Last updated on APRIL 30, 2024
Applies to:
Oracle HRMS (UK) - Version 12.2 and laterInformation in this document applies to any platform.
Symptoms
Problem Statement:
After upgraded the database to 19C, the Parse Errors for many statements are in the alert log on both RAC databases.
PAY_GB_PEOPLE_SOE_V is only one of many Parse Errors
SELECT ROWID,SALARY,JOB_TITLE, .... ....
FROM PAY_GB_PEOPLE_SOE_V
WHERE assignment_action_id = :1
Where PAY_GB_PEOPLE_SOE_V is a view with this DDL:
CREATE OR REPLACE FORCE EDITIONABLE VIEW "APPS"."PAY_GB_PEOPLE_SOE_V"
("ROW_ID", ...)
AS
SELECT DISTINCT PER.ROWID ROW_ID , PER.PERSON_ID , NULL SALARY , NULL PAY_BASIS ,
...
UNION
SELECT DISTINCT PER.ROWID ROW_ID, PER.PERSON_ID, EEV.SCREEN_ENTRY_VALUE SALARY,
...
UNION
SELECT DISTINCT PER.ROWID ROW_ID, PER.PERSON_ID, EEV.SCREEN_ENTRY_VALUE SALARY,
INITCAP(PPB.PAY_BASIS) PAY_BASIS, JBT.NAME JOB_TITLE, PER.FULL_NAME,
...
Steps to Reproduce:
The issue can be reproduced at will with the following steps:
- Upgraded the database to 19C
- Check the Alert log on RAC database
Changes
Upgraded the database to 19C
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |