My Oracle Support Banner

On 12.2.9 Patch Upgrade ends up with Errors "ORA-01452: cannot CREATE UNIQUE INDEX" On Pay_cost_allocation_keyflex_tab.xdf, Pay_payroll_actions_tab.xdf (Doc ID 2811526.1)

Last updated on AUGUST 13, 2023

Applies to:

Oracle Payroll - Version 12.2.9 and later
Information in this document applies to any platform.

Symptoms

When attempting to apply the 12.2.9 upgrade patch, the following error occurs.

Two workers of patch 28840850 have error out with the following message

ERROR
-----------------------

Worker 1:

CREATE UNIQUE INDEX "HR"."PAY~COST~ALLOCATION~KEYFLE~PK" ON "HR"."PAY_COST_ALLOCATION_KEYFLEX"...
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

Worker 2:

CREATE UNIQUE INDEX "HR"."PAY~PAYROLL~ACTIONS~PK" ON "HR"."PAY_PAYROLL_ACTIONS"....
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

STEPS
-----------------------

The issue can be reproduced at will with the following steps:

1. Apply the 12.2.9 upgrade patch

2. Observed the above error in the ADWORKER Log.

ADDITIONAL INFORMATION:
-----------------------------------

The issue reported on 12.2.9 upgrade is due to Unique Index creation on certain tables.

These Indexes were to be created on Revised Columns but failed because data was not migrated into the revised columns.

Below mentioned error was captured as part of the Log files.

ERROR MESSAGE:
---------------------

BEGIN AD_ZD_TABLE.APPLY('PAY_COST_ALLOCATION_KEYFLEX_F1'); END;

*
ERROR at line 1:
ORA-06563: name has too many parts
ORA-06512: at "SYS.DBMS_UTILITY", line 156
ORA-06512: at "SYS.DBMS_UTILITY", line 1515
ORA-06512: at "APPS.AD_ZD_TABLE", line 1764
ORA-06512: at line 1

===================================

On further analysis, it was found that the "AD_ZD_TABLE.APPLY" was ending in error due to the call to "DBMS_UTILITY.WAIT_ON_PENDING_DML" ending in error.

The same error was reported when call to "DBMS_UTILITY.WAIT_ON_PENDING_DML" was done using a independant PL/SQL block, as in the sample given below.

Changes

 

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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.