I am trying to store some data in an Oracle database. For a simple example, I have 2 tables I want to create, A and B, where table A has an ID which should be selected from A_sequence.nextval and each entry in table B has a foreign key linking to the id in table A.
If I read a couple of CSV files into KNIME, how can I insert entries into tables A and B where I can correctly get back the newsly assigned A_sequence values so the table B entries can link to the correct entry?
There is probably something I'm missing, but I couldn't see an easy way to do this.
You are right. There really isn't an easy way to do this. you have several options.
1. Transactionally unsafe (not recommended)
Use database writer to write into parent table, then use database reader to read out last id of your sequence. Use that to insert child rows
SELECT
last_number as "ID"
FROM
user_sequences
WHERE
sequence_name = 'YOUR_SEQUENCE_NAME'
If your KNIME workflow is the only application inserting data and you are the only person doing, then this works just fine. Albeit it will never scale.
2. Create a Stored procedure / function
In oracle create a stored procedure. Note that the database reader node executes an type of SQL. So you can then use the database reader node in a "table row to variable loop" and it calls said stored procedure with your variables. Issue: Your procedure needs to use "PRAGMA AUTONOMOUS_TRANSACTION;" or you get an error.
3. Pl/SQL in reader node
The reader node can run pl/sql code BUT the whole statement must be on 1 line!
This may or may not be suitable depending on the complexity of the SQL. You will still need to use a loop.
4. java snippet
Never tried this but you could just do all the SQL code in a java snippet.