Apex Data Load : 2 number columns from csv file select 1 based on the condition and load into table

Question

I have a issue in Oracle Apex Data Load which I will try to explain in a simple way:

I want to copy csv data (copy/paste) in Data Load Application and apply the transformation, and rules and load the data into the table BIKE.

csv columns (type, amount-a, amount-b)
```
blue, 10, 100
green, 20, 200
```
table BIKE columns (type, amount)

I want to create a transformation to check if the column value in the table BIKE is 'blue' then load amount-b other wise amount-a.

Can anyone help me on this?

Thanks

Hi, can you share a code sample of what you have tried so far? — Nora
A simple data load transformation cannot stop the loader from loading amount-a and amount-b as separate columns. The easiest approach here will be to load the CSV into a staging table, then add a PL/SQL process to load from the staging table into the target table, along with your transformation rule. — Jeffrey Kemp
type, amount-a and amount-b are in csv data but in the bike table there are only 2 columns type and amount and I want to populate amount based on what is in the type value. if value of type is 'blue' then insert type and amount-b into bicke table otherwise insert type and amount-a. — Tahir Baloch
Regarding staging table option, I think I can try that too, I can create a staging table write pl/sql code but do not know how to read my rules within pl/sql for processing. also I have not created any page that run process. if you can point me to any example will be great! thanks Jeffrey. — Tahir Baloch

Jeffrey Kemp Jeffrey Kemp · Accepted Answer · 2019-02-13T11:54:44

Create a staging table, e.g. like this (you will need to adjust the details of the columns to match your data model):

create table bike_staging
(
   apex_session number not null,
   bike_id number not null,
   bike_type varchar2(100) not null,
   amount_a number,
   amount_b number
);

Add a trigger to populate session_id:

create or replace trigger bi_bike_staging
before insert on bike_staging for each row
begin
  :new.apex_session := v('APP_SESSION');
end bi_bike_staging;

Add two processes to the third page of the data load wizard, on either side of the "Prepare Uploaded Data" process, like this:

The code for "delete staging table" will be like this:

delete bike_staging where apex_session = :APP_SESSION;

The code for "load bikes" may be something like this:

merge into bikes t
using (select bike_id,
              bike_type,
              case bike_type
              when 'BLUE' then amount_b
              else amount_a
              end as amount
        from bike_staging
        where apex_session = :APP_SESSION
       ) s
on (t.bike_id = s.bike_id)
when matched then update set
  t.bike_type = s.bike_type,
  t.amount = s.amount
when not matched then insert (bike_id, bike_type, amount)
values (s.bike_id, s.bike_type, s.amount);

delete bike_staging where apex_session = :APP_SESSION;

Alternatively, if you are only inserting new records you don't need the merge, you can use a simple insert statement.

Apex Data Load : 2 number columns from csv file select 1 based on the condition and load into table

1 Answers