APEX Upgrade From 18.2 To 21.1 Fails With ORA-02298 On APEX_210100.WWV_FLOW_ITEM_HELPTEXT_FK (Doc ID 2819149.1)

Last updated on AUGUST 04, 2022

Applies to:

Symptoms

Upgrading apex from 18.2 to 21.1 fails with below errors :

Full log can be seen in file apex_upgrade_xxx_to_xxx.log

However when upgrading the same 18.2 installation to 19.2 everything is upgraded without issues.

All constraints are valid in current APEX_180200 schema

SQL> select status,validated,count(*) from dba_constraints where owner ='APEX_180200' and constraint_type!='V' group by status,validated;

STATUS VALIDATED COUNT(*)
-------- ------------- ----------
ENABLED VALIDATED 4122

Changes

Issue introduced with Bug 30191880 in APEX 20.1 ,Part of this fix is deletion of some meta data from the “Page Items” table, which the Page Item Help Text table references to.

The corresponding help texts are not deleted as well. To verify use the following SQL Query connecting as SYS User.

select i.id, i.name, i.flow_step_id, i.flow_id, h.help_text
  from apex_180200.wwv_flow_step_items i, apex_180200.wwv_flow_step_item_help h
 where i.id            = h.flow_item_id
   and i.flow_step_id is null 

ID NAME                     FLOW_STEP_ID              FLOW_ID HELP_TEXT
-------------------- --------------- -------------------- -------------------- ----------------------------------------------------------------------------------------------------------
  17097711544963644 P_CAMPAIGN_ID            111 Campaign ID is a required field. You can search for several Campaign ID:s. Use comma to separate ID:s for example: 10,20,30
  17131874873963688 P_CAMPAIGN_ID            114 Campaign ID is a required field. You can search for several Campaign ID:s. Use comma to separate ID:s for example: 10,20,30

The two items are page items without a page ID (which the data model did allow in earlier versions). However, such items have no use at all; this is orphaned meta data (application items are stored in a different table).

The upgrade to APEX 20.1 or higher deletes these items, however, it does not delete assigned item help text. Thus we see the error message when APEX enables all constraints towards the end of the upgrade.

Cause

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