Calling Oracle Procedure which returns Ref Cursor AS output PARAMETER in ado.net
Step 1) Create a Oracle Procedure which returns Ref cursor.
Creating a Procedure which returns Ref cursors as Output Parameter.
CREATE OR REPLACE Procedure REFSP(p_orders OUT sys_refcursor)
as
begin
open p_orders for select * from pearson.orders;
end;
Step 2) Calling Oracle Procedure with ouput param IS Ref Cursor in ADO.NET
Using ExecuteReader.
protected void CallingOracleProcedure ()
{
try{
String connString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
OracleConnection conn = new OracleConnection(connString);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "scott.refsp";
OracleParameter oraP = new OracleParameter();
oraP.OracleDbType = OracleDbType.RefCursor;
oraP.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(oraP);
OracleDataReader reader = cmd.ExecuteReader(); ;
while (reader.Read())
{
Console.WriteLine(reader.GetValue(0).ToString());
Console.WriteLine(reader.GetValue(1).ToString());
//repeat for remaining fields.
}
reader.Close();
}
try{
String connString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
OracleConnection conn = new OracleConnection(connString);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "scott.refsp";
OracleParameter oraP = new OracleParameter();
oraP.OracleDbType = OracleDbType.RefCursor;
oraP.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(oraP);
OracleDataReader reader = cmd.ExecuteReader(); ;
while (reader.Read())
{
Console.WriteLine(reader.GetValue(0).ToString());
Console.WriteLine(reader.GetValue(1).ToString());
//repeat for remaining fields.
}
reader.Close();
}
catch (OracleException ex)
{
}
}
{
}
}
Method 2: Calling Oracle Procedure with Return Type as Ref Cursor in ADO.NET
Using OracleDataAdapter.
protected void CallingOracleProcedure ()
{
try{ String connString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
OracleConnection conn = new OracleConnection(connString);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "scott.refsp";
OracleParameter oraP = new OracleParameter();
oraP.OracleDbType = OracleDbType.RefCursor;
oraP.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(oraP);
OracleDataAdapter ad = new OracleDataAdapter(cmd);
OracleCommandBuilder cb = new OracleCommandBuilder(ad);
System.Data.DataSet ds = new System.Data.DataSet();
ad.Fill(ds);
{
try{ String connString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
OracleConnection conn = new OracleConnection(connString);
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "scott.refsp";
OracleParameter oraP = new OracleParameter();
oraP.OracleDbType = OracleDbType.RefCursor;
oraP.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(oraP);
OracleDataAdapter ad = new OracleDataAdapter(cmd);
OracleCommandBuilder cb = new OracleCommandBuilder(ad);
System.Data.DataSet ds = new System.Data.DataSet();
ad.Fill(ds);
}
catch (OracleException ex)
{
catch (OracleException ex)
{
}
}
}
Tags:Calling Oracle Procedure which returns Ref Cursor in
ado.net,Calling Oracle Procedure which returns Ref Cursor in ado.net
using ExecuteReader,Calling Oracle Procedure which returns Ref Cursor in
ado.net using OracleDataAdapter, Calling Oracle Procedure s in
ADO.NET,Executing Oracle Procedure s in ado.net.oracle 11g