Oracle sequence

mmanes · Post by **mmanes** » Mon May 27, 2013 7:25 am

Hi,
how can I insert a oracle sequence with DataStage 8.1?

tks for help

Poovalingam · Post by **Poovalingam** » Mon May 27, 2013 8:03 am

We can invoke oracle sequence within transformer stage or we can call SequenceName.nextval in insert sql.

mmanes · Post by **mmanes** » Mon May 27, 2013 8:07 am

How we can do it within transformer?

ray.wurlod · Post by **ray.wurlod** » Mon May 27, 2013 4:24 pm

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.

mmanes · Post by **mmanes** » Mon May 27, 2013 4:36 pm

Ok, but I have to insert the value in the field into transformer stage.

ray.wurlod · Post by **ray.wurlod** » Mon May 27, 2013 4:44 pm

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.)

mmanes · Post by **mmanes** » Mon May 27, 2013 4:58 pm

OK I'm sorry.
I cannot spend $99.
Thank you anyway.

chulett · Post by **chulett** » Mon May 27, 2013 5:09 pm

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?

mmanes · Post by **mmanes** » Mon May 27, 2013 5:20 pm

Because I no need to write code and it is easy for maintenance.

chulett · Post by **chulett** » Mon May 27, 2013 5:35 pm

So not a "need" after all but rather a preference. Fair enough.

mmanes · Post by **mmanes** » Mon May 27, 2013 5:44 pm

anyway... now I "need" to find another way.
Thank you.

chulett · Post by **chulett** » Mon May 27, 2013 5:52 pm

Why not take the user-defined SQL route using NEXTVAL? That or a Sequence Generator?

ray.wurlod · Post by **ray.wurlod** » Mon May 27, 2013 8:07 pm

mmanes wrote:I cannot spend $99.

Less than 30c per day. How much do you spend on coffee?

spoilt · Post by **spoilt** » Tue May 28, 2013 3:17 am

Use transformer Stage Property.

Surrogate tab.
SourceType = DbSequence.
Database = oracle
user / password / server.
Source name = your sequence name

=> Derivation: NextSurrogateKey()

mmanes · Post by **mmanes** » Tue May 28, 2013 3:53 am

Ray,
my work situation is critical but no matter, I'll find a way to make a clean development without workaround.

Thank you anyway.

Oracle sequence

Oracle sequence

Re: Oracle sequence