Oracle APEX Data Loader Part 1: Adding Custom Columns 2


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.


Leave a Comment

2 thoughts on “Oracle APEX Data Loader Part 1: Adding Custom Columns

  • Chupacabra

    I am trying to use the APEX data loader to allow users to import data and one of the column values I would rather not force the users to provide since the value is readily available in the session when the users selects import.Is there a way to add that column value programmatically?

    • jrweth Post author

      Yes there is. Just follow the steps above but in the loop you just need put the following:

      FOR UPLOAD_ROW IN (SELECT SEQ_ID
      FROM APEX_COLLECTIONS
      WHERE COLLECTION_NAME = 'SPREADSHEET_CONTENT')
      LOOP

      APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
      p_collection_name => 'SPREADSHEET_CONTENT',
      p_seq => UPLOAD_ROW.SEQ_ID,
      p_attr_number => '[# of the order of the column you ware inserting]',
      p_attr_value => [put session value here]);

      END LOOP;

      The only other trick is if you are allowing column headers than you will have to make sure that during the first loop you must set the value of “p_attr_value” to the column name you want to programmatically insert instead of the session value.

      This also assumes you know how many columns the files will have and can just take the column onto the end.