Tuesday, 11 March 2014

Data load for multiple tables

The data load wizard was first introduced in Apex 4.1. It lets you upload data, from let’s say a csv file, into your database. The data load capability comes with a restriction: you can upload your data only to one table.

So what if your uploaded file contains data that should be stored in more than one table? Consider the following data:

This csv file contains both data from EMP table (EMPNO, ENAME), as well as from the DEPT table (DNAME). Out of the box you will not be able to upload the csv content to the database, because you can only upload to one table a time.

As a solution you can create a view on the EMP and DEPT tables and use that view to upload your data:

Of course you can’t insert, update or delete from a view directly, but you can create instead of triggers on a view to handle such DML tasks. The instead of triggers will help us to map the content from the csv file to the appropriate tables.

Now, when we would try to upload our csv file, with the instead of trigger the EMPNO and ENAME column will be mapped to the EMP table and the DNAME column will be mapped to the DEPT table. The DEPTNO column will be inserted or updated in both the EMP and the DEPT table.

For more info on instead of triggers, read the oracle documentation
For a guide on data loading in Apex, check the builders guide