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:

	EMPNO, ENAME, DEPTNO, DNAME
	7369, SMITH, 20, RESEARCH
	7499, ALLEN, 30, SALES

view raw csvExample hosted with ❤ by GitHub

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:

	create or replace view emp_dept_v
	as
	select e.empno as empno
	, e.ename as ename
	, e.deptno as deptno
	, d.dname as dname
	from emp e
	, dept d
	where e.deptno = d.deptno
	;

view raw empDeptVw hosted with ❤ by GitHub

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.

	create or replace
	trigger emp_dept_briu
	instead of insert or update on emp_dept_v
	referencing new as n
	old as o
	for each row
	declare
	l_deptno emp.deptno%type;
	begin
	case
	when inserting
	insert into emp
	( empno, ename , deptno)
	values
	( :n.empno, :n.ename, :n.deptno )
	;
	insert into dept
	( deptno, dname )
	values
	( :n.deptno, :n.dname )
	;
	when updating
	update emp
	set ename = :n.ename
	, deptno = :n.deptno
	where empno = :n.empno
	;
	update dept
	set dname = :n.dname
	where deptno = :n.deptno
	;
	else
	null
	;
	end case
	;
	end
	;

view raw insteadOfTrigger hosted with ❤ by GitHub

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