Update table column using transformation rule in Oracle APEX 5.0

Question

I am a beginner to Oracle APEX 5 and need your help in transformation rule creation.Below is the problem scenario:

I have an csv sheet (test.csv) which contains 3 fields , let say c1 , c2 and c3 with 100 records which I need to upload to Oracle table. I have a table MYTAB in Oracle database which contains 4 columns let say c1,c2,c3 and c4.The fields c1,c2 and c3 from csv are mapped to the columns c1,c2 and c3 of database table MYTAB. I have created a data upload page where I am able to upload the csv data to Oracle table.Now during the csv upload , I want to create a transformation rule such that during the csv upload , the column c4 of table MYTAB shall be updated with a specific value of field c1 which is there in csv.

test.csv

c1     c2      c3

101    xx       aa

102    yy       bb

103    zz       kk

104    mm       yy

Expected result in Oracle table after csv upload

 Mytab

c1     c2      c3     c4

101   xx      aa    101

102   yy      bb    101

103   zz     kk     101

104   mm     yy     101

Could you please suggest how this can be done? What syntax needs to be followed here? Any help appreciated.

Bahadirs Bahadirs · Accepted Answer · 2015-08-19T12:51:57

actually i dont know how to do in apex but you may create a trigger for new record insert action on table as below and populate C4 with a simple select query in existing data

create or replace trigger "MYTABLE_TRIGGERNAME"
before insert on "MYTABLE"
for each row
begin
if :NEW."C4" is null then select C1 into :NEW."C4" from MYTABLE where C1=....; end if; end;

Update table column using transformation rule in Oracle APEX 5.0

1 Answers