INSERT option in ORACLE Enterprise Stage

sush26283 · Post by **sush26283** » Thu Sep 09, 2010 12:44 pm

was wondering if anyone can give an insight if there is an INSERT option in the ORACLE Enterprise stage as a WRITE method....

ray.wurlod · Post by **ray.wurlod** » Thu Sep 09, 2010 5:08 pm

Isn't that the default when you choose direct write over SQL? Doesn't it invoke sqlldr under the covers?

vmcburney · Post by **vmcburney** » Thu Sep 09, 2010 10:29 pm

You need to choose the Upsert method, then copy the Update statement into the clipboard and change it to an Update Only method and then paste the Insert statement into the Update field. There is no Insert only method in this stage so you need to trick it. I guess they expected insert only streams to use the load functions rather than inserts. This is fixed in version 8.1 where you can use the Oracle Connector stage to define insert and update statements more easily and with the connector migration tool that lets you convert enterprise stages to connector stages automatically.

chulett · Post by **chulett** » Fri Sep 10, 2010 6:51 am

That's good to know. I always thought it was odd the games you had to play for simple inserts and haven't had a chance yet to work on a system with connector stages yet.

sush26283 · Post by **sush26283** » Mon Sep 13, 2010 8:28 am

vmcburney wrote:You need to choose the Upsert method, then copy the Update statement into the clipboard and change it to an Update Only method and then paste the Insert statement into the Update field. There is no Insert only method in this stage so you need to trick it. I guess they expected insert only streams to use the load functions rather than inserts. This is fixed in version 8.1 where you can use the Oracle Connector stage to define insert and update statements more easily and with the connector migration tool that lets you convert enterprise stages to connector stages automatically.

Thanks Man!!!