Skip to Main Content
  • Questions
  • Can i make Output parameters as optional - i dont want to send out parameters while executing stored procedure

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Mohammed.

Asked: July 26, 2017 - 10:35 am UTC

Last updated: July 26, 2017 - 1:31 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

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));




with LiveSQL Test Case:

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks For Info

Mohammed Nayeem, February 08, 2018 - 1:34 pm UTC

Thanks For Info

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library