ORAOCI9 and sequences

kh · Post by kh » Tue Sep 09, 2003 5:20 pm

We intend to use DataStage for integrating data between OLTP applications. Many of the target tables are in Oracle and have primary keys that are typically populated (by the OLTP application) using a sequence (sequence_name.nextval).

Is there a way to get the ORAOCI9 stage to do this with a user-defined SQL statement? (Example: INSERT INTO table (id, fld1, fld2) VALUES (myseq.nextval, :1, :2)

It seems like this should be fairly simple to do, but as soon as you're forced to enter the table name on the General tab, your user-defined query is lost (replaced with a generated one).

Is there a way to do this? How about a work-around?

Regards, Kevin

chulett · Post by **chulett** » Wed Sep 10, 2003 8:12 am

This really shouldn't be an issue. [?] Once you select an Update Action of 'User Defined SQL', the Table Name on the General tab is essentially ignored. Under the 'SQL' tab, the 'Generated' tab should be disabled and the 'User-defined' tab should be active - and stay that way unless you change the Update Action.

You might try putting the table name in first. Since it will be ignored when you go User Defined, you could put "DUAL" or even "USER_DEFINED" in there to help people recognize what's going on.

-craig

jseclen · Post by **jseclen** » Wed Sep 10, 2003 9:07 am

Hi Kevin,

Check this last item ...

http://www.tools4datastage.com/forum/to ... ms=nextval

Miguel Seclen
Lima - Peru