I am inserting entries in two Oracle tables. I want to relate the 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 create the relation?

erik_jan wrote:

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.

 

markatsafe wrote:

@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.

 

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).

 

erik_jan wrote:

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).

 

Perfect. Thanks