Designing a Robust File Upload Architecture in Oracle APEX

In the modern enterprise landscape, file handling is not a nice-to-have — it’s a core expectation. Whether users are uploading contracts, invoices, reports, or profile images, your Oracle APEX application must deliver a secure, scalable, and flexible file upload experience.

Oracle APEX 24.2 has significantly evolved its file handling capabilities, supporting multiple file uploads, file type restrictions, and enhanced integration with both temporary and persistent storage options. Let’s walk through all the architectural choices and implementation strategies you should consider while designing your file upload module.

Key Use Cases for File Uploads in APEX

Uploading supporting documents during form submissions
Attaching files to master-detail records (e.g., invoice and receipts)
Uploading profile pictures or resumes
Handling bulk imports (CSV, Excel)
Holding files temporarily until final save or approval

Storage Options: Choosing the Right Architecture

Depending on your use case, file volume, and retention needs, Oracle APEX supports the following storage mechanisms:

1. Database BLOB Column (Persistent Storage)

Use case: When the file must be stored permanently with the record (e.g., invoice PDFs, images).

How it works:

One simple and reliable way to upload files in Oracle APEX is by saving them directly into a table using a BLOB (Binary Large Object) column, such as ATTACHMENTS.FILE_DATA. You can use a File Browse item in your form, and when the user submits the form, APEX automatically stores the uploaded file into the table — no extra code is needed.

This method is great when you want to keep all your files inside the Oracle Database, especially for security, auditing, or backup purposes. Since the files are saved along with your application data, you don’t need a separate backup process for them.

However, be careful if you’re allowing large files or many uploads — it can make your database grow quickly and might slow things down over time. In such cases, you may need to plan for extra storage and consider database performance tuning.

2. APEX Collections (Temporary Holding Area)

In scenarios where users may upload files as part of a multi-step form or may not immediately submit the data, APEX Collections offer a flexible and session-safe way to manage file uploads temporarily.

How it works:

When a user uploads a file, instead of directly saving it into a permanent table, the application stores the file’s metadata and BLOB content in an APEX Collection. This acts as a temporary holding area linked to the user’s session.

Once the user completes the entire process (such as reviewing or confirming the form), the file is transferred from the collection to a permanent database table.

Advantages:

Temporary isolation: Uploaded files stay separate from your main data until confirmed.
Avoids unwanted inserts: No files are stored permanently if the user abandons the process mid-way.
Session-specific: Each user has a dedicated collection, avoiding data conflicts.

Considerations:

Session-based: Collection data is lost if the session ends unexpectedly.
Requires additional logic: You must explicitly write the process to move data from the collection to your permanent table.

Copy Code

declare
    l_file_names apex_t_varchar2;
    l_file       apex_application_temp_files%rowtype;
begin

  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
	p_collection_name=>'DOCUMENT'
  );

    l_file_names := apex_string.split (
                        p_str => :P28_DOC,
                        p_sep => ':' );
    for i in 1 .. l_file_names.count loop
        select *
          into l_file
          from apex_application_temp_files
         where name = l_file_names(i) and APPLICATION_ID=:APP_ID;      
         APEX_COLLECTION.ADD_MEMBER(
                 p_collection_name => 'DOCUMENT',
                 p_c001            => l_file.FILENAME,
                 p_c002            => l_file.MIME_TYPE,
                 p_blob001         => l_file.BLOB_CONTENT,
                 p_n001            => l_file.ID,
                 p_d001            => sysdate );

        -- raise_application_error(-20001,l_file_names(i));
    
    end loop;
end;

3. External Storage (e.g., Object Storage, APIs)

In this method, users choose a file using the APEX File Browse item. When they submit the form, the file is not stored in the database. Instead, it’s sent directly to an external storage service (like Oracle Cloud or AWS S3) using PL/SQL code (via tools like UTL_HTTP or DBMS_CLOUD).

At the same time, important details about the file—like its name, size, type, and link (URL)—are saved in a database table so the app can find and use the file later.

Why use this method:

It saves space in your Oracle database because files are stored outside.
It’s ideal for cloud storage or when working with many or large files.
It’s scalable, meaning it works well even if lots of users are uploading files.

Things to keep in mind:

You must set up the external storage securely, using encryption and access keys.
Make sure only the right people can access the files, and handle things like link expiry or private access.
Add error checks and logs in your process to handle any failures during file upload.

Ways to Implement File Upload in APEX

Let’s now explore all possible ways to build file upload functionality using Oracle APEX 24.2 features.

A. Upload via Form to a Table (Direct Insert)

The most common scenario:

Create a table with a BLOB column (e.g., ATTACHMENT_FILE)
Add a form region in APEX.
Add a File Browse item, map it to the BLOB column.
On submit, the file is stored in the database.

Enhance using:

MIME_TYPE, FILENAME, and LAST_UPDATED columns for metadata.
Custom validations for file types and sizes.

B. Upload via Shared Component > Data Definition

Oracle APEX allows defining File Upload Data Sources through Shared Components, especially useful in import/export flows.

Best suited for:

Reusable upload patterns.
Predefined data formats (CSV, Excel).
Staging logic before insert.

C. Upload and Hold Temporarily (Using Collections)

When your flow involves multiple steps or a confirmation process, you can capture the file using a File Browse item and store it in an APEX Collection like:

Copy Code

apex_collection.add_member (
   p_collection_name => 'TEMP_FILES',
   p_blob001      => :P1_FILE_BLOB,
   p_c001            => :P1_FILENAME
);

Later, on final save, process and move these to the target table.

Useful in: Quotation uploads, form drafts, bulk file submissions.

D. File Upload Using REST APIs

APEX allows using REST APIs for uploading files to external systems. You can:

Accept the file from the File Browse item.
Base64 encode and send via UTL_HTTP.
Save URL/reference in APEX DB.

Best Practices for File Upload

Restrict File Types
- Use APEX’s built-in settings: Allowed File Types = .pdf, .jpg, .xlsx
- Prevent .exe, .js, and other harmful types.
Enable Multiple Uploads
- Allows multiple files through enhanced File Browse.
- Store them in a child table or iterate in collections.
Use File Size Limits
- Set max file size (e.g., 10 MB) using File Browse settings.
Metadata Matters
- Capture MIME_TYPE, FILENAME, FILE_SIZE, CREATED_DATE.
Avoid Temporary Table Buildup
- If using temp tables or collections, ensure cleanup via session expiration or validation.

Wrap-Up

Building a robust file upload architecture in Oracle APEX is more than just a File Browse item — it’s a combination of:

Choosing the right storage mechanism
Designing a secure and user-friendly flow
Aligning temporary and persistent strategies

Whether you’re building a single-upload form or an enterprise-grade document module, the architecture should serve both performance and maintainability.

If you’re working on a document-heavy Oracle APEX application, structuring your file upload process right from the start will save future headaches.

Upload Multiple Files in Oracle APEX