Monday, January 23, 2006

Driver Sources for Oracle

Here is my preliminary list of what works against Oracle....

OLE DB Source - use the Oracle Driver, but be cautious of Numerics and use a data viewer to make sure that numbers to the right of the decimal come through...

Lookup - Use the Microsoft Driver...using the Oracle driver will cause mismatches of all records when comparing numbers, like when looking up keys.

SCD - Oracle OLE DB Driver...no other choice, because the Microsoft driver can't handle parameters

OLE DB command - Same as above, as you need parameters

OLE DB Destination - use the Microsoft driver, as numbers ending in 0 (zero) will be turned into zeroes!!!

Cheers!
Scott

7 Comments:

At 11:25 AM, Anonymous Colin said...

Hi,

Interesting all the problems you have been having with Oracle. I am using a custom script in the data flow task to access an Oracle SP that returns a refcursor that populates an OracleDataReader. I'm then sending this to an OLE DB destination (SQL Server - cannot get the SQL Server destination working!). Now I am getting ORA-03113 errors on big rowcounts and my dba team are telling me this is a red herring after digging around in the Oracle db and finding no problems and no network problems either. Any ideas what driver/provider I should be using?

 
At 11:52 AM, Blogger Scott Barrett said...

Colin,

If you are using parameters, then you will need to use the Oracle for OLEDB driver. There should not be an issue with large resultsets as we routinely pull 10's of millions of records. The Data Reader is going to be pretty slow, so I would go to and OLE DB Source and use a variable to create the statement that it executes.

Scott

 
At 11:17 AM, Anonymous Colin said...

The problem is that I am having to execute an Oracle Stored Procedure to return a ref cursor (business decision to enscapulate data and query) - hence using the datareader within a custom script. Can you use a ref cursor along with the ole db source?

 
At 1:33 PM, Blogger Scott Barrett said...

Colin,

You can't call it directly from the OLEDB source. I would create a stored proc on Oracle and just call it from the OLEDB Source. That source does not understand the nuances of sources that well other than SQL Server....

BTW, I have a good link on how to do this that I am trying to find for you. I will post it when I find it...

Scott

 
At 6:49 AM, Anonymous Anonymous said...

Hi Scott,

I am using Execute SQL task for reterving data from a table with CLOB field, this field is having XML. I am getting the error.
"ORA-00932: inconsistent datatypes: expected - got CLOB"

The behaviour is strange, this error occurs only when i put clob field at first position. If i move it to different position then its working.

Could you through some light on this.

I need to get records and fill it into ADO recordset for parsing the xml row by row.

 
At 7:47 AM, Blogger Scott Barrett said...

SSIS cannot handle CLOBS....sorry! You will need to find another method to extract it to file to use....

Scott

 
At 3:35 PM, Blogger Katherine said...

Hi Scott,

I'm new in SSIS and I tried to put my sql statement in a variable like you explain but I must do something wrong because It doesn't work.

When you tell us to put the sql statement in the expression of the variable do we still put the ? sign for the parameter in the sql statement ?

Also, have you found another solution since then ?

Thanks

 

Post a Comment

<< Home