topic Re: [resolved] Store value of Oracle Sequence for use in data flow in Talend Studio

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

Anonymous — Sat, 16 Nov 2024 13:41:31 GMT

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

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

Anonymous — Wed, 11 Nov 2009 01:51:27 GMT

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

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

Anonymous — Wed, 11 Nov 2009 18:38:49 GMT

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-p/109452
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.

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

Anonymous — Thu, 12 Nov 2009 04:01:53 GMT

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

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

Anonymous — Thu, 19 Nov 2009 22:02:54 GMT

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

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

Anonymous — Fri, 20 Nov 2009 02:49:34 GMT

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

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

_AnonymousUser — Wed, 10 Mar 2010 18:48:16 GMT

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

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

_AnonymousUser — Wed, 24 Jul 2013 13:22:14 GMT

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

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

Anonymous — Tue, 13 Aug 2013 23:11:23 GMT

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.

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

alevy — Wed, 14 Aug 2013 02:03:20 GMT

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.

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

_AnonymousUser — Thu, 21 Nov 2013 10:17:57 GMT

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

Hi Atul/ Shong -
Any update on the above question -
does talend have any reusable sequence generator like in Informatica.
Even I have the above scenario - and I have used tmap to generate seq.id's. but since its not reusable - all the targets are getting different sequence number.
Pls do lmk as soon as possible.
Thanks in advance
Rose