Surrogate key using Oracle sequence

[snt_ds]

Hi,

OCI --> TRN --> OCI

I have a design where I read from an Oracle table (Stg) and load in the another Oracle table with a Transformer in between. The only thing this job does is to load the tgt Oracle table with a Surrogate key from an Oracle sequence.
I am using the SeqName.NextVal as part of the insert statement itself, but it keeps failing and the log says.
"ORA-01036: illegal variable name/number"

The User defined SQL for inserts is as below.

INSERT INTO TABLE (TREASURY_PRODUCT_CODE,SOURCE_PRODUCT,SOURCE_PRODUCT_TYPE,SOURCE_PRODUCT_SUB_TYPE,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON) VALUES (Schemaname.SEQNAME.NEXTVAL,:2,:3,:4,:5,TO_DATE(:6, 'YYYY-MM-DD HH24:MI:SS'),:7,TO_DATE(:8, 'YYYY-MM-DD HH24:MI:SS'))

The sequence is created correctly in the DB.

Thanks in Advance,
NV

[chulett]

For every column defined on the Columns tab, you need to specify a numbered bind variable. You've got 8 columns defined but only 7 mentioned in the sql, hence the error.

Remove the target for the sequence from the column list (TREASURY_PRODUCT_CODE) and decrement all of your bind variable numbers by 1.

[sachin1]

hello dear please remove your column name for which you want to use a sequencer from column tab but in your user-defined sql statement do mention column name and its sequencer with proper order.

[snt_ds]

Thanks for the prompt responses..it worked.