June 21, 2010 at 2:29 pm
Does anyone have any sample scripts and/or information on how exactly to call an Oracle refcursor from SSIS? There are a lot of articles on the web about refcursors but few mention SQL Server. The few articles that I have found that talk about refcursors and SQL Server never seem to have any scripts. If anyone has anything at all, it would greatly be appreciated!
jstw
June 21, 2010 at 4:02 pm
I do not have an example for SSIS and I'm having a hard time trying to imagine why a refcursor is needed in such scenario.
Let me show you how a refcursor is used when executing a procedure from SQLPlus. Lets say procedure MYPACKAGE.MYPROCEDURE is supposed to return something so you do...
variable my_cursor refcursor;
exec MYPACKAGE.MYPROCEDURE(:my_cursor);
print my_cursor;
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 21, 2010 at 4:18 pm
Thanks for the response! I know where you're going with that, in regards to why would you call a Refcursor from SSIS rather than just do a normal data flow, etc. Trust me, I'm in total agreeance...but it wasn't exactly my choice. At this point, there's a bunch of complex logic in the Oracle procedure that no one's wanting to rewrite...but we need the data in a SQL Server database, ultimately.
Let me ask it this way: how would I execute an Oracle function or procedure that uses a refcursor via OPENQUERY? I can't get that to work either. I can pull straight select data back, no problem, but the refcursor syntax gets me every time.
On a different note, I tried the syntax that you posted directly from the Oracle db in a Toad window but I just get "ORA-00900: invalid SQL statement".
Thanks!
June 21, 2010 at 4:34 pm
Johnathon Wilde (6/21/2010)
Thanks for the response! I know where you're going with that, in regards to why would you call a Refcursor from SSIS rather than just do a normal data flow, etc. Trust me, I'm in total agreeance...but it wasn't exactly my choice. At this point, there's a bunch of complex logic in the Oracle procedure that no one's wanting to rewrite...but we need the data in a SQL Server database, ultimately.
I see. How about using that Oracle procedure on the Oracle side, insert the output to a staging table then let SSIS move the content of that staging table to the SQL Server side?
Johnathon Wilde (6/21/2010)
On a different note, I tried the syntax that you posted directly from the Oracle db in a Toad window but I just get "ORA-00900: invalid SQL statement".
refcursor syntax is for SQLPlus - not for Toad or any other GUI tool 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 23, 2010 at 12:15 pm
Johnathon
As per my knowledge you can not receive Oracle Cursor in SQL Server...It's like Honda and Toyota both have engines but you can't drive Honda with Toyota's Engine...Same with DB...SQL Server and Oracle both have Cursors but You can not receive Oracle Cursor in SQL Server and Unfortunately Cursor is the best way for Oracle to return the record set...I would suggest just have Oracle return you the PL/SQL when you call Oracle SP and you simply EXECUTE that Variable against the linked server so it will return the same record set which you are trying to receive from REF CURSOR....
FO
June 30, 2010 at 12:55 pm
I am not sure if you can do it with OPENQUERY but SSIS 2008 comes with ADO.NET source and destination Refcursors are used in ADO.NET using either the Microsoft connector or ODP.NET. There is existing code for ADO.NET you have to try if you can consume it in SSIS 2008 ADO.NET Task. Hope this helps.
Kind regards,
Gift Peddie
June 30, 2010 at 4:56 pm
Here is the Refcursor implememtation for VS2008 using the Microsoft provider with restrictions.
http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledatareader(VS.90).aspx
Kind regards,
Gift Peddie
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply