Friday, 3 February 2012

Oracle APEX 4.1 - Data Loading

In APEX 4.1 Oracle have added a new page type called "Data Loading".


The aim of this page type is to allow end users to import data into a table specified by the developer. To achieve this the end user run a Data Load Wizard that uploads data from a file or allows them to copy and paste the data directly into the wizard. You, as the developer, create a number of data load wizard pages for the end user to follow similar to those available in the APEX development environment (SQLWorkshop->Utilities->Data Workshop). Behind the scenes APEX appears to be using collections to allow the data to be manipulated before it finally loads it into the target table. As you have access to a lot of the processing that's going on then you should be able to customise both the look of the forms and the processing that goes on behind them - within reason.

In my scenario I'm going to create a data load wizard so that end users can load data into a demo table called EMP. The data they are going to use will be in the format of a file called newEmp.csv which looks like this:

When the data is loaded I want the DEPTNO column value to be checked against an existing table's values to make sure it is a valid department number and I also want the ENAME column values to be upper cased.

So let's create a data loading wizard. First of all click on the "Create Page" button off your Application Builder's home page and select the "Data Loading" icon. On the next screen specify a name for the data load definition, the database schema, the table where the data will end up and the primary key(s) column(s) for that table. Then click on "Next".

You can then set up a table lookup which allows you to match an uploaded value against another table and use the associated key value, instead of the uploaded value. 

This is quite useful for checking the integrity of the data that will be coming in. Here I'm getting it to validate the value of the DEPTNO column in the DEPT table.

Click on "Add"


Next I want to convert the ENAME values to upper case - this is called a "Transformation Rule". Out of the box there are only a few basic transformation rules (Upper/Lower case, Replacing a String, Cleaning whitespaces, Trim). So, for example, you can replace the string 'NY' with New York. I believe more advanced transformation rules will be considered for future releases - something to look forward to there I think. In the meantime as the process is using  a LOAD_CONTENT collection to hold the data prior to uploading you should be able to create your own process that changes any of the collection's content using APEX_COLLECTION.UPDATE_MEMBER and APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE.

 Click on "Add"


Click on "Next". What you will now see is that this wizard is actually creating four pages - it has filled in default values for the page names, numbers and region name - you can of course change these if you wish. Likewise you can add a breadcrumb.

Next comes the buttons - again their names are automatically filled in and you can change them if you wish. You also need to decide which pages to branch to when someone either cancels the data load or finishes it. 

Then you get to the confirmation page

So that's the wizard completed - now comes the testing part. Start off by running the first page of the wizard - in my example that's page 2.


In my example I want to upload a file so once I've clicked on the radio button I get a file browse item. My file is comma separated, my fields are encoded by ' and the first row of data is actually the column names - so I need to file in the form appropriately as below:


Click on the "Next" button and you will see it has loaded a version of the data - notice the names are still in a mixture of upper and lower case:


Click on "Next" and it will show the data validation:

In my example you will see the ENAME column values have been converted to upper case as per my transformation rule and that the second row has failed. Click on "Load Data" and it will take you to the results and explain why the second row failed.


  1. How to load the data in CSV file to multiple tables using this dataload wizard

  2. How to implement computation in the data load process. Like how can I add :APP_USER in all records or any other computation on data fields which will not be in the actual csv data?

    1. Hi Mamun
      You could create a PL/SQL trigger on the table that fires after the data has loaded to do the things you mention

  3. This comment has been removed by the author.

  4. Same question I would like to ask! How to implement computation in the data load process?

    Mobile App