RCP dataset needs to update/delete/insert an Oracle stage

[SIHM]

I have a generic job. This has a dataset with RCP and a field with the change capture in , a filter stage and an Oracle stage. Using this information, I must either insert update or delete rows in a table.

Given that I don't have the SQL, how can I set the key column so that the stage knows which rows to delete or update.

Are there any functions to do this or must I pass in an SQL statement to the job to do this.

Thanks

[ray.wurlod]

This should be available from the table definition you imported from the Oracle table.

[SIHM]

Sorry Ray. I dont understand your answer.

Yes, I can import the schema from the table, but the dataset that I have could contain possibly only 1 field in the case of a delete, x fields for an update and all fields for an insert.

Is there anyway of reading the schema of the dataset at runtime and then passing a parameter in to set one of the fields as a key ?

[ray.wurlod]

No.

Perhaps you could use a generic name for the key explicitly in DataStage, and create an alias in the database for the key column.

[chulett]

Perhaps this post might help.

[SIHM]

Thanks Craig for the post.

Finally I wrote a routine to read the Key columns and non Key columns from the partial schemas available and create SQL from that.