3 Replies Latest reply on Sep 17, 2013 12:35 PM by Epic Fail

    Data Load Wizard Lookup

    Epic Fail

      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.

        • 1. Re: Data Load Wizard Lookup
          Mike Kutz

          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.



          1 person found this helpful
          • 2. Re: Data Load Wizard Lookup
            Epic Fail

            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:

            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

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