This discussion is archived
3 Replies Latest reply: Sep 17, 2013 5:35 AM by Epic Fail RSS

Data Load Wizard Lookup

Epic Fail Newbie
Currently Being Moderated

I have three tables:

Locations

  Location_id,

  Location_Name

 

Trips

  Trip_id,

  Location_id,

  Trip_Date

 

Data_Gathered

  Data_Gathered_id,

  Trip_id,

  Sample_Number

 

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.

  • 1. Re: Data Load Wizard Lookup
    Mike Kutz Expert
    Currently Being Moderated

    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:

    1. a hidden item for the "data set key"
    2. "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
    3. 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.

     

    This means:

    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.

     

    MK

  • 2. Re: Data Load Wizard Lookup
    Epic Fail Newbie
    Currently Being Moderated

    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:

    Data_Gathered

      Data_Gathered_id,

      Trip_id,

      Sample_Number

     

    2) define an upload table in the shared component -> data loading -> "my data "load" -> table lookups with the following options:

    owner: my_schema

    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?

  • 3. Re: Data Load Wizard Lookup
    Epic Fail Newbie
    Currently Being Moderated

    I ended up solving this by creating a view with "instead of create" and "instead of update" triggers.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points