Oracle APEX Data Loader Part 1: Adding Custom Columns

APEX-Oracle-Application-ExpressI both love and hate Oracle Apex.  I love that it is so easy to get a simple project up and running. I hate that it is so hard when diverging from the standard practices.

Recently I was charged with creating a way to import data into a table by uploading a text file. I stumbled across the Data Loader Wizard. This seemed to be a very handy way to allow users to import a data text file directly into a database table.   Sounds great!  However, in my case it wasn’t that simple.

The data that I needed to import was actually just a list of CONTACT IDs.  The table that I was uploading into included the CONTACT ID and a whole bunch of other fields that the user needed to be able to input manually during the upload process.  I also needed to do some custom validation which doesn’t come out of the box. So this is what I wanted to happen:

  1. User navigates to page to upload list of contact ids.
  2. User chooses additional fields to be inserted into the table
  3. User is presented with any validation errors
  4. User confirms upload

Adding Custom Columns

The first challenge was to add some custom columns defined by user input to each row in the upload. To do so I performed the following steps:

  1. Create Data Loader Wizard:

    In Application Builder select “Create Page->Data Loading” and run through the wizard to get a base definition setup for the data loader. The end result was 4 new pages in my Application for running the Data Loader (we’ll call them P1, P2, P3, P4).

  2. Add User Input Fields to Upload Page:

    Add the particular user inputs as items on P1 of the Wizard.  This included drop downs, text inputs etc.

  3. Create a Process to insert the additional Fields:

    Part of P1 of the data loader wizard is a process that handles getting the data from the file (or cut and paste text area) into an Apex Collection.  It is named “Parse Uploaded Data.”  This built in process takes the rows of the upload and inserts them into the ‘SPREADSHEET_CONTENT’ Apex collection.  I added another process “Add Additional Fields” that runs after “Parse Uploaded Data” to add the additional user inputted fields to the SPREADSHEET_CONTENT collection.  In the process I loop through all of the rows that were in the input file and set additional columns (2, 3 and 4) to the values supplied by the user from item on P1.  Here is a sample of the code.  I actually stuck this in a package, which I would suggest, but here I reference the items directly.

 

So voila!  I was able to add user supplied input to each row without too much hassle.  Stay tuned for further posts on how I skipped the column mapping step and performed some row validation on the imported rows.