Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to automatically insert a default value to a column during spreadsheet data loading?

DannyS-OracleApr 19 2017 — edited Apr 19 2017

Hi guys,

This is my first time using the Data Loading Wizard pages in Apex, so I am a bit lost when trying to customize its workflow

So I have a CHECKLIST_ITEM table which lists all checklist items of a checklist (identified by the ID_Checklist column):

IDID_CHECKLISTChecklist_Content
1121Clean my room
2121Brush my teeth

I want to create a data loading wizard from a checklist page. So when I open the Data Loading Wizard, I already have the ID of the checklist (e.g. 121) and store it in an item (e.g. P1_ID_CHECKLIST).

Meanwhile, my spreadsheet data will not have the ID_CHECKLIST column, only the Checklist_Content column. It looks like this:

Checklist_Content

Eat healthy breakfast

Go to work

So my question is, how to automatically insert the P1_ID_CHECKLIST value to the ID_CHECKLIST column for each row in the imported spreadsheet data?

Any help will be appreciated!

P.S. I am using Apex v5.1.

Comments

LA County APEX

Usually, I used a staging table instead of loading the data directly into the working table so that I can clean up the data after the data has been loaded into the staging table.

However, if you can try to use a trigger by fetching the check list id from the lookup table.

For example:

create or replace trigger bi_my_tbl

before insert on my_tbl

for each row

begin

if :new.id_checklist is null then

   begin

     select id_checklist

       into :new.id_checklist

       from CHECKLIST_ITEM

        where Checklist_Content = :new.Checklist_Content

           and rownum = 1;

   exception when others then

     null;

   end;

end if;

end;

1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 17 2017
Added on Apr 19 2017
1 comment
476 views