Oracle APEX 18.x to 19.1 upgrade issue

Oracle APEX 18.x to 19.1 upgrade issue

 

ORA-01422: exact fetch returns more than requested number of rows?

 

Error while upgrading to Oracle APEX 19.1

After doing few upgrades to 19.1 release that went smoothly this example surprised me. It was one of the questions on HROUG 2019.

Keeping it simple running appins.sql script this is the error that showed up: 
        ok 375 - Installing Advisor  Metadata                                |   0,00
    ok 376 - Installing Verification Images                             |   0,00
    ok 377 - Installing Packaged Apps Metadata                          |   0,00
    ok 378 - Installing Packaged Apps                                   |   0,22
    ok 379 - Updating App Owner/Version                                 |   0,00
    not ok 381 - Copying Instance Settings                              |   0,02
    # Message: ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at "APEX_190100.WWV_FLOW_UPGRADE", line 5630
    # ORA-06512: at "APEX_190100.WWV_FLOW_UPGRADE", line 5716
    # ORA-06512: at line 4
    #
    # Statement: begin
    # if '2' = '2' Then
    #     sys.dbms_output.put_line('-- Copying preferences to new schema. -------');
    #     wwv_flow_upgrade.copy_prefs('APEX_180200','APEX_190100');
    #     commit;
    # end if;
    # end;
    # Message: ORA-06512: at "APEX_190100.WWV_FLOW_UPGRADE", line 5630 ORA-06512
: at "APEX_190100.WWV_FLOW_UPGRADE", line 5716
    # ORA-06512: at line 4
    #
    # Statement: as above
    # Message: ORA-06512: at "APEX_190100.WWV_FLOW_UPGRADE", line 5716 ORA-06512
: at line 4
    #
    # Statement: as above
    # Message: ORA-06512: at line 4
    # Statement: as above
    ok 382 - Enabling WS Constraints                                    |   0,00
    ok 383 - Dropping Upgrade Triggers in APEX_180200                   |   0,02
not ok 2 - 382 actions passed, 1 actions failed                         |   3,47
begin
*
ERROR at line 1:
ORA-20001: Install errors found in phase 2
ORA-06512: at "APEX_190100.WWV_INSTALL_API", line 440
ORA-06512: at line 8 

After digging around it seemed it was breaking at:  
    Elapsed: 00:00:00.02 # 

# Copying Instance settings 

# PL/SQL procedure successfully completed. 

-- Copying preferences to new schema. ------- 

begin 

* 

ERROR at line 1: 

ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "APEX_190100.WWV_FLOW_UPGRADE", line 5630 

ORA-06512: at "APEX_190100.WWV_FLOW_UPGRADE", line 5716 

ORA-06512: at line 4

which was taking part in last script appins.sql was executing
@^PREFIX.core/scripts/install_action.sql "Installing Packaged Apps" @^PREFIX.core/packaged_apps/install_packaged_apps.sql

 
It came down to APEX_180200.WWV_FLOW_PLATFORM_PREFS table which contained two rows.

After deleting one of the rows upgrade run fine.

There if you experience the same issue this could be the same core reason why it failed. 

Only note here database this was done on was on site Oracle Standard Edition. Thanks to Ivica M. J. for sharing this with us.

Happy APEXing,
Lino