I have three tables:
Many pieces of data are gathered for each trip and many trips are taken to each location. This forms a 1:M relationship between Locations and Trips and between Trips and Data_Gathered. I am using surrogate keys as the primary key for all tables.
I need to create a data load wizard on the Data_Gathered table.
The data being uploaded would look kinda like:
Location, Date, Sample_Number, ....
With Trip_id and Sample_Number uniquely identifying records in the Data_Gathered table.
It appears like I need to do a lookup inside a lookup which isn't possible. I also thought about creating a view to serve as a lookup but Apex does not allow that.
I am running Apex 4.2.2 and 11gR2.
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:
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?