Page 1 of 1
Oracle sequence
Posted: Mon May 27, 2013 7:25 am
by mmanes
Hi,
how can I insert a oracle sequence with DataStage 8.1?
tks for help
Re: Oracle sequence
Posted: Mon May 27, 2013 8:03 am
by Poovalingam
We can invoke oracle sequence within transformer stage or we can call SequenceName.nextval in insert sql.
Posted: Mon May 27, 2013 8:07 am
by mmanes
How we can do it within transformer?
Posted: Mon May 27, 2013 4:24 pm
by ray.wurlod
You can use an Oracle sequence in user-defined SQL, for example
Code: Select all
INSERT INTO myschema.mytable(C1,C2,C3) VALUES mysequence.NEXTVAL, ORCHESTRATE.C2, ORCHESTRATE.C3);
I leave it to Poovalingam to explain how to invoke within a Transformer stage; it's not something I'd do directly.
Instead, I would connect a Surrogate Key Generator stage to the Oracle sequence, and use the appropriate function to get the next surrogate key within the Transformer stage.
Posted: Mon May 27, 2013 4:36 pm
by mmanes
Ok, but I have to insert the value in the field into transformer stage.
Posted: Mon May 27, 2013 4:44 pm
by ray.wurlod
I did address that in my response. You need to get yourself a premium membership to be able to view the entire response from the five premium posters. (This is the mechanism by which DSXchange is funded.)
Posted: Mon May 27, 2013 4:58 pm
by mmanes
OK I'm sorry.
I cannot spend $99.
Thank you anyway.
Posted: Mon May 27, 2013 5:09 pm
by chulett
mmanes wrote:Ok, but I have to insert the value in the field into transformer stage.
Why do you "have to" do it in a transformer?
Posted: Mon May 27, 2013 5:20 pm
by mmanes
Because I no need to write code and it is easy for maintenance.
Posted: Mon May 27, 2013 5:35 pm
by chulett
So not a "need" after all but rather a preference. Fair enough.
Posted: Mon May 27, 2013 5:44 pm
by mmanes
anyway... now I "need" to find another way.
Thank you.
Posted: Mon May 27, 2013 5:52 pm
by chulett
Why not take the user-defined SQL route using NEXTVAL? That or a Sequence Generator?
Posted: Mon May 27, 2013 8:07 pm
by ray.wurlod
mmanes wrote:I cannot spend $99.
Less than 30c per day. How much do you spend on coffee?
Posted: Tue May 28, 2013 3:17 am
by spoilt
Use transformer Stage Property.
Surrogate tab.
SourceType = DbSequence.
Database = oracle
user / password / server.
Source name = your sequence name
=> Derivation: NextSurrogateKey()
Posted: Tue May 28, 2013 3:53 am
by mmanes
Ray,
my work situation is critical but no matter, I'll find a way to make a clean development without workaround.
Thank you anyway.