Is it possible to return an Oracle Ref Cursor to a caller that is in SqlServer T-SQL? When dealing with a normal .Net program there is this Knowledge Base article: http://support.microsoft.com/kb/322160
But is this same type of thing possible from T-SQL?
I believe this is what you ar looking for http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/fcdaa97e-8415-4c3e-8ffd-1ad45b590d57/ (from there):
We can use Script Component to execute oracle stored procedures with ref cursor as parameter(s)
Add the output columns to the component.
For eg: I had a oracle package Get_Employees.GetEmployees which takes an put put parameter of type ref cursor
I added 2 such columns EmpID and EmpName and then override CreateNewOutputRows() as
Public Overrides Sub CreateNewOutputRows()
Try
Dim dr As OracleDataReader = oracleCmd.ExecuteReader()
While dr.Read
Output0Buffer.AddRow()
Output0Buffer.EmpID = CDec(dr(0).ToString)
Output0Buffer.EmpName = dr(1).ToString
End While
Catch ex As Exception
Me.ComponentMetaData.FireError(-1, "InitExtract", ex.Message, String.Empty, 0, True)
End Try
Output0Buffer.SetEndOfRowset()
End Sub
Prior to that we need to set oracle command object in PreExecute subroutine as:
Dim plsql As String = "BEGIN Get_Employees.GetEmployees(:curEmployeesbyID);END;"
oracleCmd = New OracleCommand(plsql, oracleConn)
With oracleCmd
.CommandType = CommandType.Text
.Parameters.Add(":curEmployeesbyID", OracleType.Cursor).Direction = ParameterDirection.Output
End With
