title: Call an Oracle Function from C# with Nulls
body: <p>I'm trying to call an Oracle Function from our C# application, but I either get the following errors. I think I have two problems: </p>
  
  <ol>
  <li><p>I want to call this function, but some of the parameters can be null on the C# side so I don't know how to handle them.</p></li>
  <li><p>I don't know if I need to add the return value to the parameters with <code>ParameterDirection.ReturnValue</code> on the <code>OracleParameter</code> object.</p></li>
  </ol>
  
  <p>This is what I'm trying:</p>
  
  <pre><code>public int GetActivityRowCount(DateTime fromDate, DateTime thruDate, string grpCds, string catCds, string typCds, long? memNbr, long? subNbr, string searchBy, string dispActivity, string statCds, bool showUncategorized, string debugYN)
  {
      OracleCommand cmd = null;
  
      try
      {
          StringBuilder sql = new StringBuilder();
          sql.Append(" pack_SomePack.func_SearchRowCount");
  
  
          cmd = new OracleCommand(sql.ToString(), this.Connection);
          cmd.CommandType = CommandType.StoredProcedure;
  
          // Don't know if I should add this guy
          // cmd.Parameters.Add(new OracleParameter("RowCount", OracleDbType.Int16, ParameterDirection.ReturnValue));
  
          cmd.Parameters.Add(new OracleParameter("FromDate", OracleDbType.Date, fromDate, ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("ThruDate", OracleDbType.Date, thruDate, ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("grpCds", OracleDbType.Varchar2, grpCds, ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("catCds", OracleDbType.Varchar2, catCds, ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("typCds", OracleDbType.Varchar2, typCds, ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("memNbr", OracleDbType.Long, memNbr, ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("SubNbr", OracleDbType.Long, SubNbr, ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("searchBy", OracleDbType.Varchar2, searchBy, ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("dispActivity", OracleDbType.Varchar2, dispActivity, ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("statCds", OracleDbType.Varchar2, statCds, ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("showUncategorized", OracleDbType.Char, showUncategorized? "Y" : "N", ParameterDirection.Input));
          cmd.Parameters.Add(new OracleParameter("debugYN", OracleDbType.Varchar2, debugYN, ParameterDirection.Input));
  
          cmd.BindByName = true;
  
          int activityRowCount = Convert.ToInt16(cmd.ExecuteScalar()); // Error here
  
          return activityRowCount;
      }
  </code></pre>
  
  <p>My function does the following:</p>
  
  <pre><code>FUNCTION func_SearchRowCount
  (
      in_FromDate         IN DATE,
      in_ThruDate         IN DATE,
      in_GrpCds           IN VARCHAR2,
      in_CatCds           IN VARCHAR2,
      in_TypCds           IN VARCHAR2,
      in_MemNbr           IN Actv.PersNbr%TYPE,
      in_SubNbr           IN Actv.SubNbr%TYPE,
      in_SearchBy         IN VARCHAR2,
      in_dispActivity     IN VARCHAR2,
      in_StatCds          IN Ams.StatCd%TYPE,
      in_UncategorizedYN  IN CHAR,
      in_DebugYN          IN CHAR
  ) RETURN NUMBER AS
  
      lvnCount            NUMBER;
      lvsSqlStr           VARCHAR2(2000);
  
  BEGIN
      lvsSqlStr := 'SELECT COUNT(*) FROM SomeTable WHERE (Include a bunch of clauses..)';
      BEGIN
          EXECUTE IMMEDIATE lvsSqlStr
                  INTO lvnCount
                  USING (All the parameters);
      END;
  
      RETURN lvnCount;
  
  END func_SearchRowCount;
  </code></pre>
  
  <p>I get the following error when running what's above. </p>
  
  <p><code>PLS-00306: wrong number or types of arguments in call to 'FUNC_SEARCHROWCOUNT'</code></p>
  
  <p>All the variables are bound with the correct amount, although I read somewhere that ODP.NET will remove the parameters with <code>null</code> as there <code>.Value</code>. Is this true? What should I pass in to indicate that there is no value for that parameter then?</p>