Below is my simple dummy procedure
create or replace procedure sp_dummy
(
p_options NUmber,
cursorparam1 OUT sys_refcursor,
cursorparam2 OUT sys_refcursor
)
AS
begin
open cursorparam1 for
select sysdate from dual;
open cursorparam2 for
select p_options from dual;
end;
Below is my C# code for executing this sp
public static DataTable getDT()
{
DataTable dt = null;
try
{
DataSet ds = new DataSet();
using (OracleConnection con = new OracleConnection(GetConnectionString()))
{
{
con.Open();
using (OracleCommand cmd =new OracleCommand("sp_dummy", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("p_options", OracleDbType.Int, 0));
cmd.Parameters.Add(new OracleParameter("cursorparam1", OracleDbType.RefCursor, 0, ParameterDirection.Output));
cmd.Parameters.Add(new OracleParameter("cursorparam2", OracleDbType.RefCursor, 0, ParameterDirection.Output));
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
da.Fill(ds);
}
}
}
}
catch (Exception ex)
{
throw;
}
return dt;
}
This is working fine for and am getting two results in dataset
My question is can i avoid sending output parameters? if i do like this its giving me wrong number of arguments error message
cmd.Parameters.Add(new OracleParameter("cursorparam1", OracleDbType.RefCursor, 0, ParameterDirection.Output));
cmd.Parameters.Add(new OracleParameter("cursorparam2", OracleDbType.RefCursor, 0, ParameterDirection.Output));
Nope. As stated in the docs, you need to specify (in) out parameters:
Parameter Mode Is Default? Role
IN Default mode Passes a value to the subprogram.
OUT Must be specified. Returns a value to the invoker.
IN OUT Must be specified. Passes an initial value to the subprogram and returns an updated value to the invoker.
http://docs.oracle.com/database/122/LNPLS/plsql-subprograms.htm#LNPLS659