Monday, November 28, 2005

Parameterized queries against Oracle...

So I needed to get some data out of Oracle the other day and need it to be based off some parameters. Turns out the OLE DB source will not recognize parameters when used against Oracle. Neither the Microsoft or Oracle driver will do it. So I thought I might still be able to use the DataReader source against Oracle, it won't support parameters at all. I ended up creating a workaround that went like this....

First, create a variable called MyStmt and make it a String. Create another variable, MyID as an Integer. Now go to the properties of the MyStmt variable. Set the "EvaluateAsExpression" property to True. Now, read this well...place the SQL statement that you want to create in the "Expression" property, not the Value of the Variable. You will have to do some data conversion to concatanate the string and integer, so email me if you need help with that. Now, go back to your OLE DB source and set the source of the SQL statement to a Variable, and choose the correct variable name. Voila! A parameterized query! Don't forget to use the Microsoft OLE DB for Oracle driver and to check the datatypes on the Numerics...see my earlier blog posts...

Hope this helps....

Upcoming post...how to get SSIS to work against Oracle on a 64-bit server...

Scott
scott(at)barrettzone.com

6 Comments:

At 2:10 AM, Anonymous Jamie Thomson said...

Scott,
I wouldn't call that a workaround. I'd call it the right and proper way of going about this. I ALWAYS use this technique, regardless of whether I am going against Oracle or not.

-Jamie

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

Jamie,

I've found another method that will allow us to do the same thing without putting the SQL statement in a variable. I'll throw a post up today about it.

I agree, this is the easiest way to do this, but not as well documented as I would like.

Thanks for the post that you put up about my blog. I appreciate the help!

Scott

 
At 6:50 PM, Anonymous Sergio Rodríguez said...

Scott,

Thanks for this BLog, it has some very useful tips.

Do you have any information for how to make work an Oracle OLE DB connection from SSIS in a Windows 2003 x64 edition? ... I have problems with this situation...

Thanks!

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

Sergio...

Remember that in order to use the 64bit components, you must move the SSIS package to the server and then launch it from a SQL Server Agent job. Running it manually will cause it to run in the 32bit workspace and it won't see the Oracle drivers. When it is launched from the SQL Server Agent, it is in the 64bit workspace and will see the Oracle drivers

 
At 5:41 PM, Blogger Wenbiao said...

Scott,
I have a "similar" problem, except I need to update records into an Oracle Database.

My situation is: I have two tables in an Oracle database, Days and C_DAY, and both have data, I need to read some records from C_DAY and strcat 4 columns for each row and then update the matching row in the DAYS table, using an OLE DB Command component.

I can not figure out how to update the Oracle database, in the OLE DB Command component, I tried to use a SQL statment, such as:
update DAYS set SAFETY=? where PrimaryKey=?

but it does not support parameterized SQL statement, with OLE DB Connection to Oracle.

Could you please help?

Thanks!
Wenbiao

 
At 3:42 PM, Blogger Scott Barrett said...

Check which driver you are using. I believe only one of the drivers will support the parameters. I think I have a post on here that shows which one to use.

Scott

 

Post a Comment

<< Home