how to use an oracle sequence to generate surrogate key

jreddy · Post by **jreddy** » Wed Feb 18, 2004 7:50 am

Guys,

Can anyone help me with this issue. I need to use an oracle sequence (key.nextval) to assign to a ID field on the target table. I basically have a single oracle source, a transformer and a final target oracle table. Now what stage/derivation should i add to be able to assign that nextval of the sequence to the ID on target table.

Please suggest. Thanks in advance.

chulett · Post by **chulett** » Wed Feb 18, 2004 8:06 am

Your Job Type say Parallel. I don't know if this will be relevant if this is a PX job. Do you need to know the sequence number during the job (doesn't sound like it) or can it just be used on the target side?

If you need to know it you can set up a Lookup of one column using custom sql that does the 'select key.nexval from dual'. Do not specify a key and simply use the output in your transform.

Or you can use custom sql in the target OCI stage. Do not include the sequence's target column in your stage, but include it in your custom sql. Instead of using a passed in value (:1, :2, etc) use "key.nextval" for that one column. Oracle will automatically supply the proper value when the insert is perform by the database.

Again, this is a Server answer and it probably doesn't work anything like this in PX.

jreddy · Post by **jreddy** » Wed Feb 18, 2004 8:17 am

Yeah, actually thats what i tried meanwhile, i wrote a user-defined sql on source oracle stage, with just the sequence name as one column. It worked fine. I am sure we can do it at the target end also.

The lookup part .. Since my design has one source, one target oracle stage and one transformer, if i need to add a lookup stage (corresponding to another oracle table).. where should i put it up? the transformer will not take more than one reference or stream link. since i already have one source ora stage, it doesn't accept the link from lookup stage:(

chulett · Post by **chulett** » Wed Feb 18, 2004 8:41 am

Sure you can!

The Transform won't accept more than one Input stream, but will support any number of Reference and Output streams.

Simply drop a new OCI stage on the canvas and drag a line from the OCI stage to the Transform. You'll be fine.

jreddy · Post by **jreddy** » Wed Feb 18, 2004 11:13 am

unfortunately no.., atleast according to the manuals too.. the transformer can accept only one input (reference/stream irrespective) in a parallel job.. such a big constraint on development of parallel jobs...

raju_chvr · Post by **raju_chvr** » Wed Feb 18, 2004 12:36 pm

Are you sure about it? Did u try it in a Paralell job?

I don't have access to PX. but I assume that won't be the case even in Parallel jobs as it totally beats the concept of DS and DW..

I think u might be using DSBASIC transformer instead of regual Transformer. Check it once more

Teej · Post by **Teej** » Wed Feb 18, 2004 1:00 pm

jreddy wrote:such a big constraint on development of parallel jobs...

Nah, not a constraint at all. The biggest problem with Server developers switching to PX is the use of Transformer. You are to use the Transformer LESS, not more, than you would if you do it Server-wise. Get away from the Transformer, look at the other stages, and play with it.

You know, I really feel like a dumbass right now. Someone told me flat out that .NEXTVAL is not possible on PX about 18 months ago. I took it as a religious fact.

Now I just ran a small test that I should have ran a long time ago. Yup, jreddy is right -- input AND output (although you will need to do update only, not table load -- but easy enough for input) can do this.

*sigh* Time to retrain folks here.

-T.J.