How to get the value of a sequence from an Oracle stage.
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
How to get the value of a sequence from an Oracle stage.
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.
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.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 69
- Joined: Tue Jan 18, 2005 12:15 am
Why do you have to do a lookup again? Can't you get the seq.nextval in your source itself?
Something like
Possible ???
Something like
Code: Select all
select A.Col1, A.Col2, A.Col3, (select seq.nextval from dual) AS Unique_No from Table A
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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...
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
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
maheshsada wrote: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..................
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
Re: How to get the value of a sequence from an Oracle stage.
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.Minhajuddin wrote: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.
That should work for you.
Narasimha Kade
Finding answers is simple, all you need to do is come up with the correct questions.
Finding answers is simple, all you need to do is come up with the correct questions.
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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
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
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
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
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
Re: How to get the value of a sequence from an Oracle stage.
Hi Chulett,Minhajuddin wrote: I want to insert values with a sequence in a table and a flat file.
Please help me out.
Thanks a lot.
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.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers