How to retrieve APEX app from older version after the upgrade

When upgrading Oracle APEX to a newer version (e.g., from 22.1 to 23.2), you might notice that the previous APEX schemas (like APEX_220100, APEX_220200) remain in your database even after the upgrade is complete. At least if you’re lucky enough that the DB didn’t do the job as it should 😉.

This can be a lifesaver if your application was accidentally removed from the current APEX environment.

The Solution

1) Connect to the older schema (e.g. APEX_220200):

ALTER USER APEX_220200 IDENTIFIED BY APEX_220200;
ALTER USER APEX_220200 ACCOUNT UNLOCK;

2) Create a table to store exported app (in APEX_220200 schema)

CREATE TABLE custom_export (
  c CLOB
);

3) Export the app. Replace:

• WORKSPACE_NAME - with the correct workspace name

• APP_ID - with ID of the application you want to export

DECLARE
  v_export wwv_flow_t_export_files;
BEGIN  
  htmldb_util.set_workspace(:WORKSPACE_NAME);
  v_export := wwv_flow_export_api.get_application(p_application_id => :APP_ID);
  INSERT INTO custom_export VALUES (v_export(1).contents);
  COMMIT;
END;
/

…and you are DONE! ✅

Save the exported application to the SQL file and import it into your current APEX environment.

The Correct Way

Do the regular backup!