topic Re: [resolved] Oracle Sequence current value in Talend Studio

[resolved] Oracle Sequence current value

Anonymous — Thu, 28 May 2015 12:53:35 GMT

Hi,
I have a table and its primary key is generated with oracle sequence.
In my case I am inserting few columns in that table.(I gave sequence in additional columns ... I worked)
after few operations on data i need to update few columns on the same record. In order to do that I need current value of oracle_sequence .(as its my primary key)
Is that possible???
I know i can do it with join.But I need current value of oracle_sequence.
when i try "sequence.currval" i get

ORA-00957: duplicate column name

Thanks,
Pankaj

Re: [resolved] Oracle Sequence current value

Anonymous — Thu, 28 May 2015 21:45:07 GMT

No this is a very bad idea because you can never be sure anybody else is selecting another value of the sequence.
The only reliable way is to select a value from the sequence with seq.nextval und keep this value in your job for further processing. Anything else is a design flaw!

Re: [resolved] Oracle Sequence current value

Anonymous — Fri, 29 May 2015 06:36:29 GMT

Hi,
Thanks for your reply.
according to my knowledge i can get same sequence using seq.currval for that session.In order to get currval i have to first use seq.nextval for that session.(I had same doubt as you so i performed a simple test in oracle DB).
I am not hung up on currval i just need same sequence i used to insert that record so i can use it in update query.

select a value from the sequence with seq.nextval and keep this value in your job for further processing. Anything else is a design flaw!

How can i store sequence.nextval in global variable???
an example will really help.

Thanks,
Pankaj

Re: [resolved] Oracle Sequence current value

Anonymous — Fri, 29 May 2015 09:04:06 GMT

If you want to use the sequence for several things inside your job, I would get the next sequence number (as jlolling suggested) using a query against "DUAL". Return that value to the job and use a tSetGlobalVar component to store the value. The tSetGlobalVar will store the value in a global HashMap called "globalMap". You can store values in this using the tSetGlobalVar component or manually using a bit of Java (see below)...

globalMap.put("VariableKey", variable);

You can retrieve these values practically anywhere in Talend using a variant on the code below....

((String)globalMap.get("VariableKey"));

In the example above, we are casting the value to a String as all values are stored as Objects. You will need to cast to whatever type you start with. The "VariableKey" is the name of the value.
This should allow you to retrieve the value once from the sequence and use it as many times as you wish in the Job.

Re: [resolved] Oracle Sequence current value

Anonymous — Fri, 29 May 2015 10:46:32 GMT

Hi,
how to fire query to DB.what component should i use???
(I know to to get data from table (i knw toracleinput))

Thanks,
Pankaj

Re: [resolved] Oracle Sequence current value

Anonymous — Fri, 29 May 2015 10:53:40 GMT

You can use the tOracleInput component for this. Change the query to ....

"Select seq.nextval as keyNum
From DUAL"

...and make sure you set the schema to be one column of a numeric type

Re: [resolved] Oracle Sequence current value

Anonymous — Fri, 29 May 2015 13:23:47 GMT

Hi,
It worked!!
thanks rhall and jlolling for your help.
regards,
Pankaj