Here it is a way for ref cursors in MS SQL Server from Oracle:

1) Little bit of Oracle DBA assistance may be needed
2) VS studio would help in creating C# class and deploying
3) Ability to create Oracle packages
4) Ability to create SQL Server packages
5) SQL Server DBA assistance for configuring and approval
6) Follow steps step1-step5.

//--step1----------------------------- create Oracle pkg-------------------------------------------
CREATE or REPLACE PACKAGE m04_get_pgm_info_pkg AS

TYPE tpk_return_refcur IS REF CURSOR;

PROCEDURE m04_get_pgm_info (
pi_subj1 IN VARCHAR2, pi_subj2 IN VARCHAR2, pi_subj3 IN VARCHAR2, pi_subj4 IN VARCHAR2
,pi_subj5 IN VARCHAR2, pi_subj6 IN VARCHAR2, pi_subj7 IN VARCHAR2, pi_subj8 IN VARCHAR2
,pi_subj9 IN VARCHAR2, pi_subj10 IN VARCHAR2, pi_subj11 IN VARCHAR2, pi_subj12 IN VARCHAR2
,po_return_refcur IN OUT tpk_return_refcur
);
END m04_get_pgm_info_pkg;
//--step2----------------------------- create Oracle pkg body--------------------------------------
//--note this procedure returns 3 rows of a single columned string
CREATE or REPLACE PACKAGE BODY m04_get_pgm_info_pkg AS
PROCEDURE m04_get_pgm_info (
pi_subj1 IN VARCHAR2, pi_subj2 IN VARCHAR2, pi_subj3 IN VARCHAR2, pi_subj4 IN VARCHAR2
,pi_subj5 IN VARCHAR2, pi_subj6 IN VARCHAR2, pi_subj7 IN VARCHAR2, pi_subj8 IN VARCHAR2
,pi_subj9 IN VARCHAR2, pi_subj10 IN VARCHAR2, pi_subj11 IN VARCHAR2, pi_subj12 IN VARCHAR2
,po_return_refcur IN OUT tpk_return_refcur
) IS
.
.
.
.
OPEN po_return_refcur FOR
select 'RESULTS'
from dual
union all
select '-----------------------------------------------------------------------------------'
from dual
union all
select 'Ran Successfully; number of rows/records created=' || count(*)
from myschema.m04_pat_prog;
END m04_get_pgm_info;

END m04_get_pgm_info_pkg;
/
//--step3-------------------------- create C# Class-------------------------------------------------
//create a SqlServerProject2.dll using below class via Visual Stuido, by deploying it.
//change TNSNAME,USERID,PASSWORD to appropriate values in the connection string
//note: the class name is “StoredProcedures2”, it has a “s”, where as the method does not.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Data.OracleClient;

public partial class StoredProcedures2
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure2(String subj1
, String subj2
, String subj3
, String subj4
, String subj5
, String subj6
, String subj7
, String subj8
, String subj9
, String subj10
, String subj11
, String subj12
//, SqlString subj12
)
{
// create connection
OracleConnection conn = new OracleConnection("Data Source=TNSNAME;User Id=USERID;Password=PASSWORD;");

// create the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "m04_get_pgm_info_pkg.m04_get_pgm_info";
cmd.CommandType = CommandType.StoredProcedure;

// add the parameters for the stored procedure including the REF CURSOR
// to retrieve the result set
cmd.Parameters.Add("pi_subj1", OracleType.VarChar, 7).Value = subj1;
//cmd.Parameters[0].Direction = ParameterDirection.Input;
cmd.Parameters.Add("pi_subj2", OracleType.VarChar, 7).Value = subj2;
cmd.Parameters.Add("pi_subj3", OracleType.VarChar, 7).Value = subj3;
cmd.Parameters.Add("pi_subj4", OracleType.VarChar, 7).Value = subj4;
cmd.Parameters.Add("pi_subj5", OracleType.VarChar, 7).Value = subj5;
cmd.Parameters.Add("pi_subj6", OracleType.VarChar, 7).Value = subj6;
cmd.Parameters.Add("pi_subj7", OracleType.VarChar, 7).Value = subj7;
cmd.Parameters.Add("pi_subj8", OracleType.VarChar, 7).Value = subj8;
cmd.Parameters.Add("pi_subj9", OracleType.VarChar, 7).Value = subj9;
cmd.Parameters.Add("pi_subj10", OracleType.VarChar, 7).Value = subj10;
cmd.Parameters.Add("pi_subj11", OracleType.VarChar, 7).Value = subj11;
cmd.Parameters.Add("pi_subj12", OracleType.VarChar, 7).Value = subj12;
cmd.Parameters.Add("po_return_refcur", OracleType.Cursor).Direction =
ParameterDirection.Output;

// open the connection and create the DataReader
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();

// Create the record and specify the metadata for the columns.
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Rownumb", SqlDbType.Int),
new SqlMetaData("Symb", SqlDbType.NVarChar, 10),
new SqlMetaData("Msg", SqlDbType.NVarChar, 250));

// Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(record);

int x = 0;

// output the results and close the connection.
while (dr.Read())
{
x++;

for (int i = 0; i < dr.FieldCount; i++)
{

// Set values for each column in the row.
record.SetInt32(0, x);
record.SetString(1, ">->");
record.SetString(2, dr[i].ToString());
}

// Send the row back to the client.
SqlContext.Pipe.SendResultsRow(record);
}
conn.Close();

// Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd();
}
};

//--step4---------- create SQL Server stored procedure and configure ------------------------------
SELECT name, database_id, is_trustworthy_on FROM sys.databases -- shows trustworty on/off

alter database yourSQLDB set trustworthy on
alter authorization on database ::yourSQLDB to sa

--enable clr integration using Surface Area Configuration tool or do the following then
--make sure you reconfigure as follows
--EXEC sp_configure 'clr enabled' , '1'
--go
--reconfigure;

–-drop only if recreating or may be you can use ALTER Assembly instead
--drop Assembly StoredProcedures2Assembly;

create Assembly StoredProcedures2Assembly
From '\\mydirectory\SqlServerProject2\SqlServerProject2\obj\Debug\SqlServerProject2.dll'
with permission_set=unsafe

drop proc StoredProcedure2Proc
create proc StoredProcedure2Proc
@subj1 nvarchar(7)='',@subj2 nvarchar(7)='',@subj3 nvarchar(7)='',@subj4 nvarchar(7)='',@subj5 nvarchar(7)=''
,@subj6 nvarchar(7)='',@subj7 nvarchar(7)='',@subj8 nvarchar(7)='',@subj9 nvarchar(7)='',@subj10 nvarchar(7)=''
,@subj11 nvarchar(7)='',@subj12 nvarchar(7)=''
AS EXTERNAL NAME
StoredProcedures2Assembly.StoredProcedures2.StoredProcedure2


//--step5--execute SQL Server stored procedure (which gets data from oracle stored
//--procedure that returns a result set via ref cursor
exec dbo.StoredProcedure2Proc
@subj1='EDU',@subj2='EDAD',@subj3='MAE',@subj4='ENE',@subj5='SSE'
,@subj6='SCE',@subj7='FLE',@subj8='MPE',@subj9=' ',@subj10=' '
,@subj11=' ',@subj12=' '


Results from the above procedure when run in SQL Server management Studio looks like this (note-there are 3 columns here a) Rownumb b) Symb c) Msg. Due to cut & paste into this forum, lost positionality. pl/sql stored procedure returned what is shown in column “Msg”) :

Rownumb Symb Msg
1 >-> RESULTS
2 >-> -----------------------------------------------------------------------------------
3 >-> Ran Successfully; number of rows/records created=13898

Source

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'OraServ', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'OraServ', @optname=N'collation name', @optvalue=N'SQL_Latin1_General_CP1251_CI_AS'
GO