[resolved] Obtaining value of Oracle Sequence to use elsewhere

Not applicable

[resolved] Obtaining value of Oracle Sequence to use elsewhere

Hi all
I am having an issue attempting to insert into an Oracle table from a tMap and also then append that same row into a hash table for future use.  This means I don't have to select from the Oracle table to re-populate the hash.  The problem I have is that the key on the Oracle table is "ID" and is populated by a sequence via a database trigger (or using "Additional Columns" in the tOracleOutput component, both work).  I need this ID value to insert into the hash table as well as the rest of the data from the tMap.  My job is simple and looks like this:
Source Table --main--> tMap & Lookups --main--> Dest Table --main--> HashOutput  (Screen print provided)
Obviously my first port of call was Google however I do not seem to be able to get the detail I need to understand how I can get a sequence value per row prior to (sequence.NEXTVAL) or even after (sequence.CURRVAL) the insert into the destination table.
The following posts were either of no use or I cannot access the content of the attachments that shows me how the components such as tFlowToIterateor tjava have been really used:
http://www.talendforge.org/forum/viewtopic.php?id=5827
http://www.talendforge.org/forum/viewtopic.php?id=8824
http://www.talendforge.org/forum/viewtopic.php?id=28950
Would anyone be able to supply me with the detail I need to work out this small issue.  I am quite new to Talend and JAVA but am okay with the Oracle side of things.
Thanks very much
Craig
Talend Enterprise Data Integration Version 5.5.1

Accepted Solutions
Seventeen Stars

Re: [resolved] Obtaining value of Oracle Sequence to use elsewhere

If you need the value from the sequence in your job you have to select this value before using it in the insert statement.
On way to do this is using simply a tOracleInput with a select on this sequence like
select my_seq.nextval from dual

This additional tOracleInput could be used in the tMap as lookup but with the lookup configuration "Load each row" instead of "Load once" (which is the default setting) and this tOracleInput should use an external connection to avoid connecting for each row.
It is simple and efficent and you have the sequence value at your finger tips ;-)

All Replies
Seventeen Stars

Re: [resolved] Obtaining value of Oracle Sequence to use elsewhere

If you need the value from the sequence in your job you have to select this value before using it in the insert statement.
On way to do this is using simply a tOracleInput with a select on this sequence like
select my_seq.nextval from dual

This additional tOracleInput could be used in the tMap as lookup but with the lookup configuration "Load each row" instead of "Load once" (which is the default setting) and this tOracleInput should use an external connection to avoid connecting for each row.
It is simple and efficent and you have the sequence value at your finger tips ;-)
Not applicable

Re: [resolved] Obtaining value of Oracle Sequence to use elsewhere

Thanks, that worked perfectly and as you say totally simple!