How to Upload Files in Oracle APEX: From Table Setup to Viewing Files

File uploads are one of those features that come up in just about every Oracle APEX project you work on. Users need to attach documents, images, spreadsheets, or PDFs, and your application needs to store them reliably and make them accessible later. In this article, you will walk through the whole process from scratch, step by step.

You will cover creating the storage table, building the upload form, writing the PL/SQL to save both single and multiple files, and then building an Interactive Grid where users can browse, preview, or download their uploaded files. By the end, you will have a fully working file management module inside Oracle APEX.

Why Files Are Often Stored Directly in Oracle Database

This question comes up often. Why store files as BLOBs in Oracle instead of putting them on a file server or in cloud storage? The short answer is simplicity and data integrity.

When files are stored in the database alongside the rest of the data, backups automatically include them. There is no file system path to maintain, no sync issues, and no separate storage service to worry about. For most internal enterprise APEX applications, this approach keeps the architecture clean and manageable.

That said, if very large files or millions of records are involved, external storage like Oracle Object Storage makes more sense. But for the vast majority of APEX projects you will build, storing BLOBs in the database is the right call.

Creating the Table to Store Uploaded Files

The first thing you want to do is create a dedicated table to hold both the file content and its metadata. It is always a good idea to store the filename, MIME type, and file size alongside the BLOB itself, because that information is needed when displaying or downloading the file later.

Here is the table you can start with:

CREATE TABLE file_uploads (
  file_id       NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  file_name     VARCHAR2(400)   NOT NULL,
  mime_type     VARCHAR2(200),
  file_size     NUMBER,
  file_blob     BLOB,
  uploaded_by   VARCHAR2(100),
  uploaded_on   DATE            DEFAULT SYSDATE
);

Here is a breakdown of what each column is for:

Once this DDL is run, the table is ready. No extra directory objects or server-side configuration is needed.

Setting Up the APEX Upload Page

Now it is time to create the upload page inside the APEX App Builder. Go to your application, click Create Page, and choose a Blank Page. Give it a simple name like "File Upload" and note the page number.

On this page, add a Form region. The region does not need to be connected to any table source because all the database work is handled manually through a PL/SQL process. The form region is just there to hold the page items and keep things organized.

The basic structure of the page looks like this:

• One File Upload page item where users select their file or files
• One Button that submits the page and triggers the upload process
• One PL/SQL Process that runs when the button is clicked
• A success message that confirms the upload completed

Adding the File Upload Page Item in Oracle APEX

Inside the form region, right-click and add a new page item. The type you need here is File Upload. This is the item type that APEX provides specifically for file input, and it handles all the browser-level file selection work automatically.

Name the item P1_FILE_UPLOAD. In the Property Editor, there are a few settings worth paying attention to:

• Storage Type: Table APEX_APPLICATION_TEMP_FILES
• Allow Multiple Files: No for single upload, or Yes to allow selecting many files at once
• Allowed File Types: Leave blank for all types, or restrict to something like .pdf,.xlsx,.png
• Maximum File Size: Something reasonable like 10 MB

The Storage Type setting is the most important one. When it is set to Table APEX_APPLICATION_TEMP_FILES, APEX automatically holds the uploaded file in a temporary session table the moment the page is submitted. Your PL/SQL process then reads from that temp table and moves the file into the permanent FILE_UPLOADS table.

Also add a Button named UPLOAD_FILE and set its action to Submit Page. That button press is what kicks off the whole process.

How APEX Handles Temporary File Storage

Before writing the PL/SQL, it helps to understand what happens in the background when a user picks a file and submits the page.

APEX stores the uploaded file in the APEX_APPLICATION_TEMP_FILES view as part of the page submission. Each uploaded file gets its own row in that view. The page item value (:P1_FILE_UPLOAD) is then set to the internal name used to reference those rows in the temp table.

Here is the part where multiple files work differently. When a single file is uploaded, the page item contains one name value. When multiple files are uploaded, the page item contains all the name values joined together with a colon separator, like name1:name2:name3. This matters a lot when writing the WHERE clause in PL/SQL, which you will cover next.

Uploading a Single File on Button Click in Oracle APEX

For a single file upload, add a PL/SQL process on the page and set it to run when the UPLOAD_FILE button is pressed. The WHERE clause here uses a simple equality check because the page item holds exactly one name value:

DECLARE
  l_blob      BLOB;
  l_mime      VARCHAR2(200);
  l_name      VARCHAR2(400);
  l_size      NUMBER;
BEGIN
  SELECT blob_content,
         mime_type,
         filename,
         DBMS_LOB.GETLENGTH(blob_content)
  INTO   l_blob,
         l_mime,
         l_name,
         l_size
  FROM   apex_application_temp_files
  WHERE  name = :P1_FILE_UPLOAD;

  INSERT INTO file_uploads (
    file_name,
    mime_type,
    file_size,
    file_blob,
    uploaded_by
  )
  VALUES (
    l_name,
    l_mime,
    l_size,
    l_blob,
    :APP_USER
  );

  COMMIT;
END;

In the process settings, also fill in the Success Message field with something like "Your file has been uploaded successfully." APEX displays this in the notification area after the process finishes without errors.

Handling Multiple File Uploads in Oracle APEX

Allowing users to upload several files at once requires a small change to the page item and a corrected WHERE clause in the PL/SQL process.

Changing the Page Item Setting

On the P1_FILE_UPLOAD item, change Allow Multiple Files from No to Yes. That is the only change needed on the front end. The browser file picker now lets users select more than one file at a time.

The Correct PL/SQL for Multiple Files

This is where many developers run into trouble. Because the page item holds a colon-separated list of names when multiple files are uploaded, using WHERE name = :P1_FILE_UPLOAD will not find any rows. The entire string like name1:name2 does not match any individual name in the temp table.

The fix is to use APEX_STRING.SPLIT to break that colon-delimited value into individual rows and then use an IN clause to match each one:

BEGIN
  FOR r IN (
    SELECT filename,
           mime_type,
           blob_content,
           DBMS_LOB.GETLENGTH(blob_content) AS file_size
    FROM   apex_application_temp_files
    WHERE  name IN (
      SELECT column_value
      FROM   TABLE(apex_string.split(:P1_FILE_UPLOAD, ':'))
    )
  )
  LOOP
    INSERT INTO file_uploads (
      file_name,
      mime_type,
      file_size,
      file_blob,
      uploaded_by
    )
    VALUES (
      r.filename,
      r.mime_type,
      r.file_size,
      r.blob_content,
      :APP_USER
    );
  END LOOP;

  COMMIT;
END;

APEX_STRING.SPLIT takes the colon-delimited page item value and returns a collection of individual name values. The IN clause then matches each one against the temp table correctly, and the FOR loop inserts a separate row into FILE_UPLOADS for each file.

One more thing worth noting: this same code also works perfectly for a single file upload. Splitting a string with no colon in it just returns one value. So you can use this version of the code regardless of whether you are expecting one file or many.

Setting Up the Interactive Grid to Show Uploaded Files

With the upload side working, the next step is giving users a way to see what has been uploaded. Add an Interactive Grid region to the page and use the following SQL as its source:

SELECT file_id,
       file_name,
       mime_type,
       ROUND(file_size / 1024, 2) AS file_size_kb,
       uploaded_by,
       uploaded_on
FROM   file_uploads
ORDER BY uploaded_on DESC

Intentionally leave out the file_blob column from this query. Fetching BLOB content for every row in a grid is expensive and completely unnecessary here. The grid is just for displaying file metadata. The actual file content is only needed when a user clicks to view or download a specific file.

Also set the Interactive Grid to read-only by setting Edit Enabled to No, since this region is purely for browsing and not for editing records.

Adding a View Link Column to the Interactive Grid

The feature that ties the whole thing together is a clickable View link in each row of the Interactive Grid. Right-click in the Columns section, add a new column, and set its type to Link. Here is how you configure it:

• Column Name: VIEW_FILE
• Heading: View
• Type: Link
• Link Type: URL
• Target: target="_blank"

For the URL, point it to the AJAX Callback process you will set up on this same page. The URL passes the file ID from the current row as a page item value. It looks like this:

f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.:APPLICATION_PROCESS=DOWNLOAD_FILE:::P1_FILE_ID:#FILE_ID#

The #FILE_ID# substitution gets replaced by the actual value from each row when the grid renders. The link should be set to open in a New Window so the file opens in a separate browser tab. For the link text, use a short label or a Font Awesome icon:

<span class="fa fa-eye"></span> View

Adding an Ajax Process to Stream the File

Instead of a separate page or an ORDS endpoint, you can use an Ajax PL/SQL Process on the same page.

First, add a Hidden page item named P1_FILE_ID to the upload page. This is the item that receives the file ID from the link URL. Make sure its Value Protected attribute is set to No so the value can be set via the URL without triggering a security error.

Creating the Ajax Process

In Page Designer, go to the Processing tab, right-click in the Ajax Callback section, and add a new process. The key settings are:

• Name: DOWNLOAD_FILE
• Type: PL/SQL Code

Here is the PL/SQL code for the process:

DECLARE
  l_blob    BLOB;
  l_mime    VARCHAR2(200);
  l_name    VARCHAR2(400);
BEGIN
  SELECT file_blob,
         mime_type,
         file_name
  INTO   l_blob,
         l_mime,
         l_name
  FROM   file_uploads
  WHERE  file_id = TO_NUMBER(:P1_FILE_ID);

  owa_util.mime_header(l_mime, FALSE);
  htp.p('Content-Length: ' || DBMS_LOB.GETLENGTH(l_blob));
  htp.p('Content-Disposition: inline; filename="' || l_name || '"');
  owa_util.http_header_close;
  wpg_docload.download_file(l_blob);
  apex_application.stop_apex_engine;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    apex_application.stop_apex_engine;
END;

Here is what each part of this code does:

• owa_util.mime_header sets the Content-Type header so the browser knows what kind of file is coming
• htp.p writes the Content-Length and Content-Disposition headers
• Content-Disposition: inline tells the browser to try to display the file rather than force a download
• owa_util.http_header_close finalizes the HTTP headers before any content is written
• wpg_docload.download_file streams the BLOB bytes directly to the browser response
• apex_application.stop_apex_engine prevents APEX from writing any HTML after the file

"Let the browser decide" is the right way to think about Content-Disposition: inline. For PDFs and images, the browser renders them directly in the new tab. For file types it cannot handle natively, like Excel or ZIP files, the browser automatically falls back to a download. No extra logic is needed to handle the difference.

Here is a quick reference for how common file types behave when the View link is clicked:

Testing the Complete Upload and View Flow

Once everything is wired up, run through the full flow to make sure it works end to end. Here is the checklist you should go through:

1. Open the upload page and pick a single file using the File Upload item
2. Click the upload button and confirm the success message appears
3. Check that the Interactive Grid shows the newly uploaded file
4. Click the View link and confirm a PDF or image opens in a new tab for preview
5. Upload an Excel or ZIP file and click View to confirm the browser downloads it automatically
6. Enable multiple file selection, pick several files at once, and confirm all of them appear as separate rows in the grid
7. Click View on each row to verify the correct file opens or downloads

If the View link opens a blank tab or shows an APEX error, the most common cause is the P1_FILE_ID item having Value Protected set to Yes, which blocks the value from being set through the URL. Setting Value Protected to No on that item resolves it in most cases.

Conclusion

Building a file upload and preview feature in Oracle APEX is a well-defined process once you understand the flow between APEX_APPLICATION_TEMP_FILES and a permanent table. The table holds the files, the File Upload page item handles browser-level selection, and the PL/SQL process moves everything into place on button click.

The important fix to remember for multiple files is that the page item value becomes a colon-separated string of names, so APEX_STRING.SPLIT is needed in the WHERE clause. Skipping this step is the most common reason a multi-file upload silently does nothing.

Using an On Demand PL/SQL Process keeps everything inside APEX on the same page. There is no separate page to create and no external service to configure. The View link URL calls the process directly, passing the file ID via a hidden page item, and the process streams the file back to the browser with the right headers to enable inline preview or automatic download depending on the file type.