I am inserting entries in two Oracle tables. I want to relate the two tables based on primary key (ID). This ID is generated as a sequence in the writer. Is there a way I can get the expected "netxval" ID, so I can set the relation ID-attribute before insertion? Or can I do this in another way?
Page 1 / 1
Instead of getting the Nextval of the sequence in the writer, you can get it earlier using a SQLExecutor transformer and the SQL statement: Select <mysequence>.nextval as ID from Dual.
Then you can use this ID attribute to write to the table and the relating table.
Instead of getting the Nextval of the sequence in the writer, you can get it earlier using a SQLExecutor transformer and the SQL statement: Select <mysequence>.nextval as ID from Dual.
Then you can use this ID attribute to write to the table and the relating table.
@erik_jan will using SQLExecutor to make a query for every feature impact the data load performance? I'm not very strong in SQL, but I wonder if there is a way to do this after the data load completes. Perhaps loading 'temporary Id's into the primary & foreign key fields (using Counter), and then replacing them in bulk with the <sequence> using SQLExecutor after the data load completes.
For a huge dataset I used a different approach: read the next sequence value, use a Counter to add to that number, Insert the data and then re-create the sequence with a new value (adding the count from the Counter). But this relies on nobody else using the sequence in the meantime and is tricky.
And using staging tables, having the database add the ID from the sequence is also an option (if you are good with SQL or PL/SQL).
But for smaller datasets I use the SQLExecutor (slower, but easier to implement).
Yes @MarkAtSafe, It will slow down the workspace. And faster (but not easier) ways are available.
For a huge dataset I used a different approach: read the next sequence value, use a Counter to add to that number, Insert the data and then re-create the sequence with a new value (adding the count from the Counter). But this relies on nobody else using the sequence in the meantime and is tricky.
And using staging tables, having the database add the ID from the sequence is also an option (if you are good with SQL or PL/SQL).
But for smaller datasets I use the SQLExecutor (slower, but easier to implement).
Thanks a lot