⚡Excel to Oracle APEX Collection Made Easy – No Plugin Needed!

🚩Introduction 

• In Oracle APEX applications, users often need to upload Excel files to add or update data. Instead of using external plugins, this can be done using built-in APEX features, which keeps the application simple and easy to maintain.

• By using the apex_application_temp_files table along with PL/SQL, we can read the uploaded Excel file, process the data, and store it in an APEX collection. This gives better control over the data and keeps the application clean and efficient.

📑 Why This Approach Is Needed

In Oracle APEX applications, users often need to upload Excel files to handle large amounts of data. Using plugins for this can make the application more complex and harder to maintain. It may also create compatibility and security issues in some cases.

Handling Excel uploads without plugins is useful when:

• You want to avoid external dependencies

• The application needs to stay simple and lightweight

• Better control over data processing and validation is required

• File data needs to be securely handled within APEX

• Bulk data upload should be fast and efficient

• The application should be easy to maintain and scalable

This approach improves performance, keeps the application clean, and provides a reliable way to manage Excel data using built-in Oracle APEX features.

👉 Use Case: Upload Excel to APEX Collection Without Plugin

In Oracle APEX, users often need to upload Excel files to insert or process large amounts of data. Instead of using plugins, we can use built-in features to handle this. The uploaded file is first stored in apex_application_temp_files, then the data is extracted and saved into an APEX collection. This keeps the application simple and avoids extra tools.

In this blog, we will see how to read an Excel file from apex_application_temp_files and load the data into an APEX collection using PL/SQL. This approach makes file handling easier, improves control over the data, and enhances application performance.

Step 1: 

In the Page Designer, create a page item and select the type as File Upload and under the properies, navigate to the Storage section and select the type as Table APEX_APPLICATION_TEMP_FILES.

 Step 2: 

Once the file is uploaded using the page item, next we need to submit the page. So create a button and under the Behaviour section, select Submit Page option from the Action property to submit the page.

 Step 3: 

Create a page process, copy the below PLSQL code to parse the excel file using the apex_application_temp_files and add it to the collection. Now the uploaded data will be loaded into the Oracle APEX collection and you can use that collection in your report or you save into your table.

---- PLSQL Code to Parse Excel File and Load into Collection ----

DECLARE
  v_tbl_name VARCHAR2(15) := 'EXCEL_UPLOAD';  
BEGIN  
  IF NOT apex_collection.collection_exists(v_tbl_name) THEN
    apex_collection.create_collection(v_tbl_name);
  END IF; 

  IF apex_collection.collection_exists(v_tbl_name) THEN
    apex_collection.truncate_collection(v_tbl_name);
  END IF; 

  FOR r1 IN (
    SELECT * FROM
      apex_application_temp_files f, TABLE(apex_data_parser.parse( 
      p_content => f.blob_content, 
      p_add_headers_row => 'Y', 
      p_file_name => f.filename, 
      p_skip_rows => 1)) p 
    WHERE f.name = :P18_FILE_UPLOAD ---File Upload Page Item
  ) 
  LOOP 
    APEX_COLLECTION.add_member (
      p_collection_name => 'EXCEL_UPLOAD', --Collection Name
      p_c001 => r1.col001,
      p_c002 => r1.col002,
      p_c003 => r1.col003,
      p_c004 => r1.col004,
      p_c005 => r1.col005,
      p_c006 => r1.col006,
      p_c007 => r1.col007,
      p_c008 => r1.col008
    ); 
  END LOOP; 
END;

Step 4: 

Now save and run the page, you will see the file upload item and the button. Now upload the excel file and click on the submit button, now the data will loaded into the collection.

🔥 Conclusion

In Oracle APEX, Excel files can be processed without using any plugins. The file data is read from apex_application_temp_files and then stored in an APEX collection. This keeps the application simple, clean, and easy to manage. It also gives better control over how the data is processed. Overall, it improves performance and provides a smoother user experience.