Post reply

Call Oracle Stored Procedure With Ref Cursor Output Parameter From SSIS

  • February 3, 2009 at 2:34 pm

    #130665

    Does anyone have some examples of pulling a dataset into MS SQL Server from an Oracle Ref Cursor using SSIS? I simply want to pull the dataset from the returned cursor and dump it into a staging table. This is a snap with a t-sql procedure but I can't seem to find any solid examples using an Oracle proc.

  • July 15, 2009 at 12:28 pm

    #1025269

    I'm having the same issue. Does anyone have any information on this, by chance?

  • September 1, 2010 at 6:30 am

    #1214900

    I'm having the same issue.

  • November 22, 2010 at 4:34 am

    #1253660

    so am i

  • November 28, 2010 at 1:15 am

    #1256002

    Can you alter the the package to 'pipeline the data out'?

    Then you can use this kind of syntax to get the data out:

    SELECT * FROM TABLE(myschema.mypackage.myfunction());

  • January 11, 2011 at 2:44 pm

    #1272105

    This worked for me.

    I had my Oracle DBA take an existing stored procedure that had a refcursor output parameter and wrap around it a tabular function that took the input parameters of the Oracle stored procedure as parameters of the new function. And then the function took care of looping through the refcursor and added the resulting rows to the table output of the function.

    To SSIS it looks like a call to a SQL Server stored procedure with an resultset I could insert directly into a table in an OLEDB destination in SQL.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply