Oracle Sequence

jherr22 · Post by **jherr22** » Wed Sep 03, 2008 10:53 am

I need to get a sequence number from an Oracle sequence to use in my job. I can modify the insert/update statement in an Oracle stage to insert a sequence number, but I need that number in other stages (we are populating many tables with the same sequence number). I need to use the sequence instead of the surrogate key generator. Any suggestions for getting an Oracle sequence number to use in a job?

DSguru2B · Post by **DSguru2B** » Wed Sep 03, 2008 11:51 am

Do a sparse lookup, ping the sequence object and get the next key. Then save it, use it, do whatever you have to.
I do something similar in db2.

richclee · Post by **richclee** » Thu Sep 04, 2008 12:11 pm

SELECT sequence_name.CURRVAL FROM dual

If you run this select, it will return the current value of the Oracle Sequence.

Regards
Richard

chulett · Post by **chulett** » Thu Sep 04, 2008 12:20 pm

Which doesn't help all that much as you really want to select the NEXTVAL so it gets incremented properly.

jherr22 · Post by **jherr22** » Thu Sep 04, 2008 9:51 pm

[quote="DSguru2B"]Do a sparse lookup, ping the sequence object and get the next key. Then save it, use it, do whatever you have to.
I do something similar in db2.[/quote]

I need to get the nextval and join it with every row of an input file, in other words, each row of the input file needs to have a new nextval value joined to it. Will a sparse lookup do that? Are you able to point me to an example?

Thanks for your help!

ray.wurlod · Post by **ray.wurlod** » Thu Sep 04, 2008 10:01 pm

No, a sparse lookup will get the next value and the next value and the next value...

To get just one next value you can use a regular lookup (probably with Entire partitioning). Provide a constant in an additional column against which you can effect a faux comparison.

Code: Select all

SELECT 1,sequence.NEXTVAL FROM DUAL

chulett · Post by **chulett** » Thu Sep 04, 2008 11:58 pm

ray.wurlod wrote:a sparse lookup will get the next value and the next value and the next value...

Pretty sure that's what they want.

jherr22 wrote:in other words, each row of the input file needs to have a new nextval value joined to it.

ray.wurlod · Post by **ray.wurlod** » Fri Sep 05, 2008 12:09 am

Yet

jherr22 wrote:I need to get the nextval and join it with every row of an input file

could be read the other way.

chulett · Post by **chulett** » Fri Sep 05, 2008 12:30 am

I know. Hence the

ray.wurlod · Post by **ray.wurlod** » Fri Sep 05, 2008 1:25 am

indeed!

jherr22 · Post by **jherr22** » Fri Sep 05, 2008 7:08 am

Sorry to be so vague.

Here is what I need in more detail. For every row from my input dataset, I need to add a column that has the nextval from my oracle sequence. So, for example, I need to join row 1 of my dataset with sequence.nextval, row2 with sequence.nextval, row3 with sequence.nextval and soforth, where sequence.nextval will be different (of course) with each row.

Thanks again for all your help - I am relatively new with Data Stage, and this problem has us stumped.

jherr22 · Post by **jherr22** » Fri Sep 05, 2008 7:12 am

Sorry to be so vague.

Here is what I need in more detail. For every row from my input dataset, I need to add a column that has the nextval from my oracle sequence. So, for example, I need to join row 1 of my dataset with sequence.nextval, row2 with sequence.nextval, row3 with sequence.nextval and soforth, where sequence.nextval will be different (of course) with each row.

Thanks again for all your help - I am relatively new with Data Stage, and this problem has us stumped.

chulett · Post by **chulett** » Fri Sep 05, 2008 7:13 am

Has or had? Sparse lookup.

jherr22 · Post by **jherr22** » Fri Sep 05, 2008 8:45 am

Thank you all for your help - you guys rock!

I did not realize that the sparse lookup was an option in the Oracle stage - I was looking in the lookup stage. Works great now!

Thanks!