We are integrating our MS SQL server app with our new Oracle Financial database (10G). We are connecting to our Oracle database with a linked server using a Oracle OLE db driver. We need to insert data to an Oracle table from our own SQL table. The Oracle table has a primary key that we need to use the Oracle nextval function to determine the unique record key for our inserted records. However when we do this in a set manner we are only returning 1 value and thus don't have a unique id for each record. We would like to avoid using a cursor on the SQL side and inserting one row at a time. I have considered using the SQL rownumber function EX - "row_number() over (order by sCustID asc) as rownum" and adding that to the returned value, but I don't know if that will work if other possible users or processes are also inserting to this table at the same time.
Is there another way to get the nextval to return a new value from Oracle for each record I'm inserting without having to resort to a cursor or looping on the SQL side? Or do I need to go with my row_number option?
INSERT INTO OPENQUERY(ORACLE_AR,
(select * from openquery(ORACLE_AR_CNV_NEW_O, 'Select xxar.XX_AR_CUSTOMER_INTERFACE_ID_S.nextval from dual')) as ID,