How to get the value of a sequence from an Oracle stage.

[Minhajuddin]

Hi all,
I have a job which has 10 input columns and inserts these columns into a table and a file. Now Before I insert it into a table and a file. I add another column which is a unique no. I generate this no. from a sequence in Oracle. I tried getting this unique no by this query "select seq.nextval from dual" in a DB stage and then looking it up in the transformer. But It didn't work. Because there was no key column I was looking up to. I am really stuck here. Do you guys have any idea as to how to go about it.

I want to insert values with a sequence in a table and a flat file.

Please help me out.

Thanks a lot.

[maheshsada]

In in your input column, select a dummy column, Name - Dummy value - '1'.
When the sequence is generated - use the below query
select seq.nextval, '1' dummy1 from dual

Now you can use the dummy column as key column in transformer

regards
Magesh S

[DSguru2B]

Why do you have to do a lookup again? Can't you get the seq.nextval in your source itself?
Something like

Code: Select all

select A.Col1, A.Col2, A.Col3, (select seq.nextval from dual) AS Unique_No from Table A

Possible ???

[ray.wurlod]

If this really is a parallel job as you have indicated there may be more issues with your approach. Will you execute this query on each processing node? In that case you will retrieve different values.

Do you really need to get the value? Why not just incorporate the reference to the sequence in user-defined INSERT statement?

Code: Select all

INSERT INTO table(c1,c2,c3) VALUES (seqname.NEXTVAL,:1,:2)

[Minhajuddin]

Hi
My source is a text file and not a DB.
So I can't use a

select A.Col1, A.Col2, A.Col3, (select seq.nextval from dual) AS Unique_No from Table A

Any other ideas...

[Minhajuddin]

In in your input column, select a dummy column, Name - Dummy value - '1'.
When the sequence is generated - use the below query
select seq.nextval, '1' dummy1 from dual

Now you can use the dummy column as key column in transformer

regards
Magesh S

Are you talking about doing a lookup? If it is a lookup which column do you think we should make as the key column, And this key column should be true once for each record.........

Can you elaborate on this Mahesh..................

[chulett]

A Server lookup does not need a Key field to work, but a hard-coded dummy value will also work.

[narasimha]

I tried getting this unique no by this query "select seq.nextval from dual" in a DB stage and then looking it up in the transformer. But It didn't work.

You could pass the link from the DB stage you are using as a lookup into the column, where you want the Oracle sequence number.
That should work for you.

[Minhajuddin]

Hi all,
Thank you Chulett.
Initially I was under the impression that the key to the lookup has to be linked to the source file. Now I have hardcoded it, and its working fine.

Thank you all,
:D:D:D
:D:D:D
:D:D:D

[jreddy]

Your design is like this SF ->Transformer-> Oracle stage. In the transformer, add a dummy column SEQ_ID and set it to a default value of 1. Then in the target stage, go to the SQL code and from the SELECT statement generated, replace the text 'ORCHESTRATE.SEQ_ID' (something like that - that is generated for this dummy column) with oracleSeq.nextval

[chulett]

Besides the fact that this topic is 'Resolved' did you miss the requirement where the sequence's value needs to go to two targets?

[Minhajuddin]

I want to insert values with a sequence in a table and a flat file.

Please help me out.

Thanks a lot.

Hi Chulett,
I did mention that the sequence value needs to go to two targets. But maybe I was not able to communicate it properly. And as you said I hardcoded the value of the key column and it's working fine. Do we have any other way to do this?

Thank you.

[chulett]

Minhajuddin, you did fine. I know you mentioned that - I was actually pointing that fact out to jreddy. Their solution is fine as long as you don't need to know the sequence number that was generated. When you do - as in your case - you have to 'pull' it beforehand.