CODEWITHSUNDEEP
oracle-databaseoracle-apex
JakeRobb
JakeRobb

Reputation: 1960

Oracle APEX: can I do a Data Load into multiple tables?

I've inherited an Oracle APEX application which we use to import data into our system. The input data is sent to us by one of our customers in CSV form. I'm familiar with Oracle databases in general, but completely new to APEX. We're using APEX version 19.2.

The APEX app uses a Data Load Wizard to take in the CSV, and it copies the CSV columns into a table with matching columns.

We now have a few additional customers who wish to do this, but they are sending a completely different set of columns. The existing table has 25 columns (one key/ID column, 24 that hold values from the CSV). The 24 value columns are specific to the first customer's specific needs and are not necessarily reusable for other customers. To continue with that pattern, I would need to add 16 more columns to said table to support the second customer. I have several customers to add, and I would quickly run into the 45-column limit. No single CSV will have >45 columns; it's just a matter of the union of all columns across all CSVs exceeding that.

I would like to create a child table with IMPORT_ID, FIELD_NAME, and FIELD_VALUE columns, and have the Data Load create a record in the child table for each column in a given row from the CSV. This would allow me to support importing data from arbitrary additional customers without changes to the APEX application.

(Due to a lack of APEX experience at my company, my hope is to minimize the need for future changes. In the long term we intend to replace this app entirely with something built on a tech stack we can better support; I'm trying to bridge the timeline gap.)

Digging through the Data Load Definition UI in App Builder, I can't find a way to configure it to feed columnar CSV data into a child table. I saw that there's a community plugin repository, but I found no plugins that do this, nor anything like it. I'm continuing to dig into documentation and other resources, but so far I'm coming up empty. Anybody know if there's a way to control the import?

One thing I found is APEX_DATA_LOADER, which seems promising. If I understand correctly, I'd have to reimplement the data loader manually, without using the Data Load Wizard. Do I have that correct?

Upvotes: 0

Views: 134

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

We now have a few additional customers who wish to do this, but they are sending a completely different set of columns

If possible, try to enforce standard set of columns and their datatypes, so that all customers send data in the same format. Doing so, you'd be able to use current data load process for all of them.

If that's not possible, another approach - which shouldn't be too difficult to implement - is to create new target table for every customer and create new loading process. Access different processes via navigation menu. Additionally, create set of stored procedures (put them into a package) which will read target tables and transfer data into their final destination. Call those procedures via Apex process.

From my point of view (based on what you wrote), trying to virtually "standardize" all CSV files so that you'd use a single target table (the one you currently have) would be the least preferable way. That sounds like quite a lot of conditional stuff (if this then that; creating server-side conditions for columns in Apex; etc.) which actually might work, but would be difficult to maintain.

Upvotes: 0

Related Questions