I find that the Data Load Wizard is woefully under powered for uploading hierarchical data from a flat file.
I solved this by using a staging table and some pl/sql 'magic'
On the 3rd page of the 'wizard' add:
- a hidden item for the "data set key"
- "on load - after header" process to set the value of the "data set key" :P23_DATA_SET_KEY := schema_name.data_set_key_seq.nextval
- a process_me call to a packaged pl/sql procedure that does the translation ( schema_name.package_app.process_me( :P23_DATA_SET_KEY )
Staging table should look like the flat data but also have a "data set key".
Add a BEFORE INSERT trigger that sets :NEW.data_set_key := v('P23_DATA_SET_KEY');
A lot of problems can occur here because the Wizard requires a PK and it does not easily allow you to assign a surrogate key from a sequence.
It seems to be built for "MERGE data", not 'pure insert'.
All the 'magic' goes into the "process_me" procedure.
Make sure this is AFTER the Data Load Wizard's process
Don't for get to delete the data in the staging table when you are done with it.
Things to note:
Data Load Wizard seems to call "commit" after every row it processes.
APEX re-uses database sessions across user sessions.
GTT for staging table is useless.
You may need to limit how much you load at once.
eg a 'small' data set of 60k rows may be too much.. Oracle support is aware of this.
Post up if you have any questions.
Thanks for responding. I think I understand what your saying to do. Your solution sounds like making a plsql transformation rule that is run before the third page is loaded. That sounds good in theory. I'm having trouble understanding what is going on with the look up table columns being shown as upload options. I do the following steps and get the following results
1) create upload process for table:
2) define an upload table in the shared component -> data loading -> "my data "load" -> table lookups with the following options:
table name: Trips
return column: Trip_id
upload column: Location_id,
upload column2: Trip_Date
When I run the upload on page two (column mapping) I see trip_date and trip_id. I do not see location_id. I need any insight you have as to how apex is handling trip_id and location_id. Is it using location_id when doing the look up and just labeling it trip_id on page 2?