Written by: ilmarkerm

Category: Blog entry

Published: August 21, 2024

I previously wrote about how after successfully downgrading APEX users still get the error “Application Express is currently unavailable”. I now ran to the same issue again, with newer versions and the procedure how ORDS detects is APEX is currently being patched has changed.

This post is about downgrading to APEX 23.1 and ORDS 24.1.

After completing APEX downgrade to 23.1, ORDS 24.1 still reported that “Application Express is currently unavailable”. I followed my own previous blog and flipped the APEX internal patching status to APPLIED, but no luck this time. PANIC! I turned on tracking for ORDS sessions and this PL/SQL block seems to be the culprit.

DECLARE
    NM         OWA.VC_ARR := :1 ;
    VL         OWA.VC_ARR := :2 ;
    L_PATCH    VARCHAR2(10) := null;
    L_VERSION  VARCHAR2(100) := null;
    SCHEMA     VARCHAR2(30);
    PART1      VARCHAR2(30);
    PART2      VARCHAR2(30);
    DBLINK     VARCHAR2(30);
    PART1_TYPE NUMBER(10);
    OBJECT_NUM NUMBER(10);
BEGIN
    OWA.INIT_CGI_ENV(:3 , NM, VL);
    HTP.INIT;
    HTP.HTBUF_LEN := 63;
 
  --CHECK THE SCHEMA OVERRIDE FOR PL/SQL GATEWAY APPLICATION DETAILS
  DECLARE
    GATEWAY_DETAILS VARCHAR2(2000) := q'[ SELECT    
                                    (SELECT VALUE FROM ORDS_METADATA.USER_ORDS_PROPERTIES WHERE KEY = 'plsql.gateway.patching') as patchingValue, 
                                    (SELECT VALUE FROM ORDS_METADATA.USER_ORDS_PROPERTIES WHERE KEY = 'plsql.gateway.version') as versionValue 
                                    FROM dual]';
  BEGIN
    BEGIN
       EXECUTE IMMEDIATE GATEWAY_DETAILS INTO L_PATCH, L_VERSION;
    EXCEPTION
       WHEN OTHERS THEN
           -- ignore exception and leave l_patch as null so fallback is executed
           NULL;
    END;
 
    IF L_PATCH IS NULL THEN
    -- L_PATCH  IS NULL, EITHER AN OLDER VERSION OF APEX IS PRESENT OR APEX IS NOT INSTALLED
    -- CHECK IF APEX PRESENT
      DECLARE
        L_SQL VARCHAR2(200) := 'select patch_applied from apex_release';
      BEGIN
        DBMS_UTILITY.NAME_RESOLVE('APEX_RELEASE', 0, SCHEMA, PART1, PART2, DBLINK, PART1_TYPE, OBJECT_NUM);
        EXECUTE IMMEDIATE L_SQL INTO L_PATCH;
      EXCEPTION
        WHEN OTHERS THEN
        -- Could not access apex_release. Default l_patch to 'N' so it's still served 
          L_PATCH :='N';
      END;
    END IF;
  END;
    :4  := L_PATCH;
    :5  :=L_VERSION; 
END;

The flag that tells ORDS that APEX is currently being patched has been moved to ORDS_METADATA schema. And in my case the query indeed returned TRUE.

SELECT VALUE FROM ORDS_METADATA.USER_ORDS_PROPERTIES WHERE KEY = 'plsql.gateway.patching'

After flipping it to FALSE, downgraded APEX started working again.

UPDATE ORDS_METADATA.USER_ORDS_PROPERTIES set value='FALSE' WHERE KEY = 'plsql.gateway.patching';