[resolved] Store value of Oracle Sequence for use in data flow

[Anonymous]

Hi. I have a spreadsheet with data that will go to several oracle database tables. I must grab the sequence number from oracle and use the same sequence number in each of the tables. So far I have been successful using a tOracleInput to get the next sequence number, but only once for the entire run. I need to grab a new sequence number for each row. Which component do I use?
I have tried using tOracleRow with the propagate option, but it grabs it as a resultset and I don't know how to cast that to an int. I have used the tOracleOutput Additional Rows feature, which inserts the new sequence into the table just fine but doesn't put it into the data flow.
I am desperate for some help as this project is due by Thanksgiving!
Thanks,
Eileen

[Anonymous]

Hello

I need to grab a new sequence number for each row. Which component do I use?

On tmap. add a new column and set the expression as:
Numeric.sequence("s1",1,1)
s1 is sequence name, the first 1 is start value, the second 1 is step
Best regards
shong

[Anonymous]

Thanks for your reply. As stated in my post, I cannot use the Talend built-in sequence. I MUST grab the sequence.nextval from Oracle and store it in a context variable or put it into the data flow.
I have viewed the following post, which you seem to be pointing people with this issue to. This does not seem to address my problem, unless I'm missing something:
https://community.talend.com/t5/Design-and-Development/Use-the-result-of-Oracle-sequence-Nextval/td-...
I have also commented on this post, in which the developer seems to have the same issue as mine, but he has gotten no useable solution:
https://community.talend.com/t5/Design-and-Development/Inserting-into-multiple-tables/td-p/109134
Please help. There must be a way to do this.

[Anonymous]

Hello

I need to grab a new sequence number for each row. Which component do I use?
I MUST grab the sequence.nextval from Oracle and store it in a context variable or put it into the data flow.

Before inserting one row into table, get the last id, and after inserting one row, you can get the current seq num as last id+1, see my example,
my sequence:

create sequence s1 
minvalue 1
maxvalue 999999999
start with 1
increment by 1
nocache;

About tFlowToIterate component usage, see 5827.
Best regards
shong

[Anonymous]

Thank you so much for your answer, Shong. I was able to get exactly what I wanted by modifying your job to issue
"Select sequence.nextval from dual" in tOracleInput. I got the next sequence number for each row, so I didn't have to create a local sequence.
While this does resolve my problem, it was an advanced solution to something that should be more simple to do. Can I request that a new feature be added to the "additional columns" of tOracleOutput that would allow you to capture the result of the SQL expression Iif it is a select) and put it on the globalMap?
Thanks again!
Eileen

[Anonymous · Accepted solution]

Hello

Can I request that a new feature be added to the "additional columns" of tOracleOutput that would allow you to capture the result of the SQL expression Iif it is a select) and put it on the globalMap?

I think it is not neccessary, as you said, you can get the next sequence number via "Select sequence.nextval from dual" in tOracleInput, you can link tOracleInput to tJavaRow, eg
tOracleInput -->tJavaRow
on tJavaRow, set it to globalMap, globalMap.put("nextseq",input_row.nextseq) //nextseq is column name.
Best regards

shong

[_AnonymousUser]

I agree this should be much simpler. It is not intuitive.

[_AnonymousUser]

Hi Shong,
I have a requirement where in I have input from flat file, it needs to be loaded to say 10 tables.
There is 1 column which needs to be unique but generated through Talend as a sequence ( as my DB is not Oracle but Amazon redshift).
This column value must be fetched from the last max value of this column and then incremented by 1.
Also, same column value needs to be inserted to multiple tables. e.g. 1 row inserted into 10 tables with this column value being the same in all 10 tables.
Can you please help me out as urgent.
Thanks
Atul

[Anonymous]

Hi,
I am working on a project . My task is to load data incrementally from MS Sql to MY Sql. I am using talend 5.3. I am facing an issue to get the data incrementally. My scenario is i have Date modified , Date created , Date Deleted columns in my tables. Whenever a row is created or update datemodified column is updated. i am working on finding a way to get max date modified from my destination i.e Mysql and store it in a variable. Use that variable in my source query to filter records based on maxdatemodified which i got from mysql. Please help me with this with some screen shotes. Appreciate your help.
Sravanthi.

[alevy]

Create a new topic for a new question and if you want more people to help, I suggest you don't ask for screenshots. Most people don't have time to build example jobs for you but are happy to give you pointers on your design.