Oracle connector Update then Insert mode not working for RCP
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 30
- Joined: Wed May 15, 2013 1:22 am
- Location: Bangalore
Oracle connector Update then Insert mode not working for RCP
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.
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.
Cheers,
Schindler
Schindler
-
- Premium Member
- Posts: 536
- Joined: Thu Oct 11, 2007 1:48 am
- Location: Bangalore
Define key Columns in output connector metadata.
Thanks
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Prasoon
ETL Consultant
LinkedIn :- http://www.linkedin.com/profile/view?id ... ab_pro_top
Blog:- http://dsshar.blogspot.com/
Re: Oracle connector Update then Insert mode not working for
The answer is right there in the error message you posted.aschindler wrote:Specify at least one key column in the input schema.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Re: Oracle connector Update then Insert mode not working for
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.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Has there been a change to allow schema files to identify keys? We had this discussion last year:chulett wrote:... unless you specify the key(s) in your schema file.
viewtopic.php?t=147155&highlight=
If a key specification now exists it would definitely provide more flexibility.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
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.![Wink :wink:](./images/smilies/icon_wink.gif)
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers