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?
Solved
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?
Best answer by erik_jan
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.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+22- Contributor
- 2179 replies
- Best Answer
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.
+22- Contributor
- 2179 replies
@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).