Automated Oracle APEX Backup and Upload to Object Storage

As Oracle APEX developers, we all know how important regular backups are.
Manually exporting applications every time is risky and time-consuming.

So I decided to automate the complete backup process using Oracle APEX Automation, PL/SQL, and Oracle Object Storage.

In this blog, I’ll explain:

• What problem this solves

• Architecture overview

• How the PL/SQL code works

• How the backup is uploaded automatically to Object Storage

Problem Statement

In most projects:

• APEX application exports are done manually

• Backups may be missed

• No centralized storage for historical backups

Goal:
✅ Automatically export APEX applications
✅ Zip the export files
✅ Upload them to Oracle Object Storage
✅ Run this on a schedule using APEX Automation

Solution Overview

We use:

• Oracle APEX Automation – to schedule the job

• APEX_EXPORT – to export applications

• APEX_ZIP – to create a ZIP file

• DBMS_CLOUD.PUT_OBJECT – to upload backup to Object Storage

📌 The entire process runs without any manual intervention.

Architecture Flow

1. APEX Automation triggers on schedule (daily/weekly)

2. PL/SQL code:

   • Sets workspace context

   • Exports APEX application

   • Creates ZIP file

3. ZIP file is uploaded to Oracle Object Storage

4. Backup is safely stored with timestamp

PL/SQL Code Used

Here is the exact PL/SQL block used inside APEX Automation:

DECLARE
    v_workspace    VARCHAR2(255) := 'YOUR WORKSPACE NAME'; 
    v_blob         BLOB;
    v_files        apex_t_export_files;
    v_filename     VARCHAR2(255);
    v_mimetype     VARCHAR2(255) := 'application/zip';
    v_workspace_id NUMBER;
BEGIN
    -- Set workspace security context
    v_workspace_id := apex_util.find_security_group_id(p_workspace => v_workspace);
    apex_util.set_security_group_id(p_security_group_id => v_workspace_id);

    -- Initialize ZIP file
    sys.dbms_lob.createtemporary(v_blob, TRUE);

    -- Export application from workspace
    FOR r IN (
        SELECT application_id, workspace_display_name
        FROM apex_applications
        WHERE UPPER(workspace) = UPPER(v_workspace)
          AND application_id = 200
    ) LOOP
        v_filename := 'APPLICATION_BACKUP';

        v_files := apex_export.get_application(
                       p_application_id => r.application_id
                   );

        apex_zip.add_file(
            p_zipped_blob => v_blob, 
            p_file_name   => 'f' || r.application_id || '.sql', 
            p_content     => apex_util.clob_to_blob(v_files(1).contents)
        );
    END LOOP;

    -- Finalize ZIP
    apex_zip.finish(v_blob);

    -- Upload to Object Storage
    DBMS_CLOUD.PUT_OBJECT(
        credential_name => 'YOUR_CREDENTIAL_NAME',
        object_uri      => 'https://YOUR_OBJECT_STORAGE_URL/'
                           || v_filename || '_' 
                           || TO_CHAR(SYSDATE,'YYYYMMDD_HH24MISS') || '.zip',
        contents        => v_blob
    );

END;

📦 What This Code Does (Simple Explanation)

1️⃣ Set Workspace Context

apex_util.set_security_group_id

This ensures the export runs in the correct APEX workspace.

2️⃣ Export APEX Application

apex_export.get_application

Exports the APEX application as SQL files.

3️⃣ Create ZIP File

apex_zip.add_file apex_zip.finish

All exported files are added to a ZIP archive.

4️⃣ Upload to Object Storage

DBMS_CLOUD.PUT_OBJECT

Uploads the ZIP file to Oracle Object Storage with a timestamp.

📌 Example filename:

APPLICATION_BACKUP_20260113_230500.zip

Automating with APEX Automation

Once the PL/SQL is ready:

1. Go to App Builder → Shared Components

2. Create Automation

3. Paste the PL/SQL code

4. Schedule it:

   • Daily

   • Weekly

   • Or as per business requirement

Now your backups run automatically 🎉

✅ Benefits of This Approach

✔ Fully automated backups
✔ Secure storage on Oracle Cloud
✔ No manual effort
✔ Easy restore when required
✔ Works for PROD & NON-PROD

Best Practices

• Use separate Object Storage bucket for PROD and NON-PROD

• Restrict credentials with minimum privileges

• Retain backups using lifecycle rules

• Enable email notification on automation failure

Conclusion

With a small amount of PL/SQL and APEX Automation, you can build a robust backup solution for Oracle APEX applications.

This approach:

• Saves time

• Improves reliability

• Follows cloud best practices

If you are working on APEX production systems, this automation is a must-have.

Let me know in comments:

• If you want to extend this for multiple applications

• If you want restore automation

• Or integrate email alerts