Oracle connector Update then Insert mode not working for RCP

[aschindler]

Hi Team,
I have Type 1 table,where in new records will be inserted and if matching record foound it should update existing record.
So in target oracle connector enabled RCP and the write mode is Update then insert ,but throwing below error.

The connector could not automatically generate the WHERE clause for the UPDATE statement. Specify at least one key column in the input schema.

Will update then insert mode will not support RCP.
Please suggest alternative approach with out disabling RCP.

[prasson_ibm]

Define key Columns in output connector metadata.

[chulett]

Specify at least one key column in the input schema.

The answer is right there in the error message you posted.

[kwwilliams]

With RCP the best way to update/insert is to write the sql statements in a file, then reference the file for your sql statements. Having DataStage generate your sql statement won't work because it doesn't know the key for your data.

[chulett]

... unless you specify the key(s) in your schema file.

[kwwilliams]

... unless you specify the key(s) in your schema file.

Has there been a change to allow schema files to identify keys? We had this discussion last year:

viewtopic.php?t=147155&highlight=

If a key specification now exists it would definitely provide more flexibility.

[chulett]

Hmmm... I believe you could always specify the key in the schema file, Ray mentions the syntax in (for example) this post. The link you posted seems to indicate a different issue? Specifically, Arnd noted that the key information was not 'passed onwards'. Perhaps I read too much into what the actual error message suggested... could it have meant something else when it said 'input schema'?

I've got no way to test or play with any of this stuff anymore so at this point I'll defer to the more smarter folks here.

[ArndW]

As far as I know the solution in the linked thread is still the only way to do this.

[chulett]

I'd involve your official support provider, if you haven't gone down that path already - see what words of wisdom they have on the subject.