How not to cache in Lookups to Oracle

Why can you not turn off the caching in a Lookup against Oracle?

I have an exceedingly complicated SQL statement like this -

SELECT OBJECT_ID, OBJECT_CODE FROM OBJECT_TABLE

If I turn off the cache for a lookup I get bombarded with this rubbish-

Error 8 Validation error. DFT Load STATUS: LKP Get RESULT_NO [128]: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E14 Description: "ORA-00933: SQL command not properly ended ". Update.dtsx 0 0

Error 9 Validation error. DFT Load DAY_STATUS: LKP Get RESULT_NO [128]: OLE DB error occurred while loading column metadata. Check SQLCommand and SqlCommandParam properties. Update.dtsx 0 0

I have tried modifying the Cache SQL Statement as well, but to no avail. I am using the MSDAORA.1 provider against "Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production".

Any ideas ?

I'm not a pro in Oracle, but just to make sure you are modifying the correct SQL statement: if you don't enable memory restrictions and lookup is in Fully cached mode, the SQL statement from first tab page is used. If you enable memory restrictions and the lookup is in Partial or No-cache mode, the caching SQL query from Advanced tab is used.

It looks like you want fully cached mode - so make sure the memory restrictions are not enabled (first checkbox on Advanced page should be clear).|||

Had this problem to,

solved when I used oraoledb.oracle.1 instead of msdaora.1

|||Where did you find this option?|||

J.A.J. wrote:

Where did you find this option?

Which option? The above post refers to a different OLE Oracle driver.|||Darren, did you ever get this resolved and if so how?|||

It seems like the only option is to load this different driver. Where can you find this driver?

thanks

|||

J.A.J. wrote:

It seems like the only option is to load this different driver. Where can you find this driver?

thanks

Looks like it's an Oracle provided OLE DB driver. You can check their Website.