Skip to main content

How to Upload Multi-Sheet Excel Files into Oracle APEX

Oracle APEX · Step-by-Step Guide

Importing one Excel sheet is easy with the built-in wizard. Importing a workbook with related parent and child sheets — and keeping the keys straight — takes a bit more care. Here's the full pattern.

Excel is still the undisputed king of business data. As Oracle APEX developers, we're frequently asked to build import utilities. While importing a single-sheet Excel file in APEX is straightforward using the built-in wizard or APEX_DATA_PARSER, things get trickier when a single workbook contains multiple worksheets with relational data — like a parent header and child lines.

In this post, we'll parse a multi-sheet Excel file, assign separate database sequences for primary keys, maintain the parent-child relationship, and stage everything into APEX Collections for reporting.

The Scenario

A user uploads a single workbook containing two worksheets:

  • Parent_Department — a single row defining the main department details.
  • Child_Department — multiple rows listing child sub-departments or teams.

The goal: parse both sheets from one upload, assign a unique primary key to the parent, link each child row to that parent via foreign key, generate unique keys for the child rows, and display both in separate reports.

STEP 1 Create the Upload Page & Page Item
  • Create a new blank page (or edit an existing one) in your APEX application.
  • Add a File Browse page item named :P7_UPLOAD.
  • Set Storage Type to Table APEX_APPLICATION_TEMP_FILES and Purge Action to End of Session (or a custom cleanup).
  • Add a Submit button to trigger page processing.
STEP 2 The PL/SQL Process

Create a page process that fires On Submit. This process retrieves the file, resolves the worksheet names, clears the target collections, and parses both sheets.

PL/SQL · On Submit Process
DECLARE l_blob BLOB; l_filename VARCHAR2(400); l_parent_sheet VARCHAR2(255); l_child_sheet VARCHAR2(255); l_parent_id NUMBER; BEGIN BEGIN SELECT blob_content, filename INTO l_blob, l_filename FROM apex_application_temp_files WHERE name = :P7_UPLOAD; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20001, 'No file was uploaded, or the upload has expired. Please try again.'); WHEN OTHERS THEN raise_application_error(-20001, SQLERRM); END; FOR r IN ( SELECT sheet_display_name, sheet_file_name FROM TABLE(apex_data_parser.get_xlsx_worksheets(p_content => l_blob)) ) LOOP IF r.sheet_display_name = 'Parent_Department' THEN l_parent_sheet := r.sheet_file_name; ELSIF r.sheet_display_name = 'Child_Department' THEN l_child_sheet := r.sheet_file_name; END IF; END LOOP; IF l_parent_sheet IS NULL OR l_child_sheet IS NULL THEN raise_application_error(-20001, 'Excel sheets "Parent_Department" and/or "Child_Department" were not found.'); END IF; apex_collection.create_or_truncate_collection('PARENT_DEPT_COL'); apex_collection.create_or_truncate_collection('CHILD_DEPT_COL'); l_parent_id := parent_dept_seq.nextval; FOR r IN ( SELECT col001, col002 FROM TABLE(apex_data_parser.parse( p_content => l_blob, p_file_name => l_filename, p_xlsx_sheet_name => l_parent_sheet, p_skip_rows => 1 )) ) LOOP apex_collection.add_member( p_collection_name => 'PARENT_DEPT_COL', p_n001 => l_parent_id, p_c001 => r.col001, p_c002 => r.col002 ); EXIT; END LOOP; FOR r IN ( SELECT col001, col002, col003 FROM TABLE(apex_data_parser.parse( p_content => l_blob, p_file_name => l_filename, p_xlsx_sheet_name => l_child_sheet, p_skip_rows => 1 )) ) LOOP apex_collection.add_member( p_collection_name => 'CHILD_DEPT_COL', p_n001 => child_dept_seq.nextval, p_n002 => l_parent_id, p_c001 => r.col001, p_c002 => r.col002, p_c003 => r.col003 ); END LOOP; END;

Code Walkthrough

File retrieval. The file uploaded through :P7_UPLOAD is temporarily stored in apex_application_temp_files. We retrieve its BLOB content and filename to pass to the parser.

Resolving sheet names. Excel stores worksheet names as display labels (e.g. Parent_Department) but references them internally by sheet ID (e.g. sheet1.xml). apex_data_parser.get_xlsx_worksheets returns that mapping, so we match the user-facing name to the correct internal file.

Collection management. apex_collection.create_or_truncate_collection clears any previous upload, giving the user a fresh staging area on every submit.

Sequence-based relational keying. The parent row gets its ID from parent_dept_seq.nextval, stored in p_n001. Each child row gets its own unique ID from child_dept_seq.nextval in p_n001, and carries the parent's ID as a foreign key in p_n002 — that's what keeps the relationship intact once both sheets land in separate collections.

STEP 3 Displaying the Reports

With the data staged, use standard APEX report regions (Classic or Interactive) to display each collection.

Parent Department Report
SELECT n001 AS department_id, c001 AS department_name, c002 AS description FROM apex_collections WHERE collection_name = 'PARENT_DEPT_COL';
Child Departments Report
SELECT n001 AS child_dept_id, n002 AS parent_dept_id, c001 AS sub_dept_name, c002 AS sub_dept_code, c003 AS manager FROM apex_collections WHERE collection_name = 'CHILD_DEPT_COL';

Comments

Post a Comment

Popular posts from this blog

APEX - Tip: Fix Floating Label Issue

Oracle APEX's Universal Theme provides a modern and clean user experience through features like floating (above) labels for page items.  These floating labels work seamlessly when users manually enter data, automatically moving the label above the field on focus or input.  However, a common UI issue appears when page item values are set Dynamically the label and the value overlap, resulting in a broken and confusing user interface. once the user focuses the affected item even once, the label immediately corrects itself and displays properly. When an issue is reported, several values are populated based on a single user input, causing the UI to appear misaligned and confusing for the end user. Here, I'll share a few tips to fix this issue. For example, employee details are populated based on the Employee name. In this case, the first True Action is used to set the values, and in the second True Action, paste the following code setTimeout(function () {   $("#P29_EMAIL,#P29_...
Post a Comment
Read more

Oracle APEX UI Tip: Display Page Title Next to the APEX Logo

In most Oracle APEX applications, every page has a Page Title displayed at the top. While useful, this title occupies vertical space, especially in apps where screen real estate matters (dashboards, reports, dense forms). So the goal is simple: Show the page title near the APEX logo instead of consuming page content space. This keeps the UI clean, professional, and consistent across all pages. Instead of placing the page title inside the page body:         ✅ Fetch the current page title dynamically         ✅ Display it right after the APEX logo         ✅ Do it globally, so it works for every page All of this is achieved using:         ✅ Global Page (Page 0)         ✅ One Dynamic Action         ✅ PL/SQL + JavaScript Simple, effective, and reusable. 1️⃣ Create a Global Page Item On Page 0 (Global Page), create a hidden item:      P0_PAGE_TITLE This item wi...
Post a Comment
Read more

Building a Custom Debug Package for Oracle APEX Using PL/SQL

While developing Oracle APEX applications, debugging page processes and backend PL/SQL logic can be challenging—especially when values are lost between processes or execution flow is unclear.  Although DBMS_OUTPUT is useful, it doesn’t work well inside APEX runtime. To solve this, I built a custom PL/SQL debug Package that logs execution flow and variable values into a database table.  This approach helps trace exactly where the code reached, what values were passed, and whether a block executed or not - even inside page-level processes and packaged procedures Why a Custom Debug Package? Works seamlessly inside Oracle APEX page processes Persists debug information even after session ends Helps trace execution flow Captures runtime values Can be turned ON/OFF dynamically Does not interrupt business logic The Package consists of:- Debug Table                         -  Stores debug messages Sequence ...
Post a Comment
Read more