Return an Oracle Ref Cursor to a SqlServer T-SQL caller

Question

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?

Answer 1

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