Populate column from a drop-down list in APEX data upload wizard

Question

I added a drop-down field P1_TABLE_NAME on the Data Load Source page of the APEX Data Load Wizard, where users can choose a value. This value should be populated into the TABLE_NAME column in the database. The TABLE_NAME is also defined as a lookup value in Oracle Apex. In the paste area the users will enter various information about the tables. So the sequence should be as follows:

User Selects value in the P1_TABLE_NAME drop-down menu.
Paste something into the paste area.
Click next.
Two columns are presented in the Data/Table Mapping page first column contains the information pasted into the paste area.
Second column contains the value selected in the P1_TABLE_NAME drop down menu.

Ideally the second column contains the lookup value for that table e.g. user enters Blah and the value in the second column of the Data/Table Mapping. Maybe if APEX can be configured to extract the extra value in the same select as the other values in the 'SPREADSHEET_CONTENT'

I used the following tutorial: http://www.jrweth.com/oracle-apex-data-loader-part-1-adding-custom-columns/

Created the extra item P1_TABLE_NAME but the form on the Data/Table Mapping only displays the values entered into the paste area.

The code that I used:

FOR UPLOAD_ROW IN (SELECT SEQ_ID FROM APEX_COLLECTIONS WHERE COLLECTION_NAME = 'SPREADSHEET_CONTENT') LOOP

 APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
    p_collection_name   => 'SPREADSHEET_CONTENT',
    p_seq                     => UPLOAD_ROW.SEQ_ID,
    p_attr_number    => '2',
    p_attr_value        => : P1_TABLE_NAME);

END LOOP;

I also tried messing about with the APEX Data Validation SQL query. Hoping that maybe I can just update the query to include additional fields, which will then be included in the list of columns presented on the subsequent Data/Table Mapping page.

select n001 as row_num, wwv_flow_lang.system_message( 'DATA_LOAD.' || c049 ) as action, c001, c002, c003, c004, c005, c006, c007, c008, c009, c010, c011, c012, c013, c014, c015, c016, c017, c018, c019, c020, c021, c022, c023, c024, c025, c026, c027, c028, c029, c030, c031, c032, c033, c034, c035, c036, c037, c038, c040, c041, c042, c043, c044, c045, : P1_TABLE_NAME

from apex_collections where collection_name = 'LOAD_CONTENT' and c049 in ('INSERT','UPDATE', 'FAILED') order by seq_id

Jeffrey Kemp Jeffrey Kemp · Accepted Answer · 2019-09-06T03:29:53

The APEX Data Loader can only load into a single target table. The list of columns presented on the Data/Table Mapping page comes from the Data Load Definition, which is defined once for a single table.

One way I've overcome this limitation is to create a staging table with all the columns from all the tables, then written custom PL/SQL after the data has been loaded to move it to the user's selected target table.

Populate column from a drop-down list in APEX data upload wizard

1 Answers