title: How to call an overloaded oracle procedure via c#
body: <p>I have two procedures in a package (TEST_PAK) that are overloaded on Oracle 9i.  One takes a string and one takes an array as parameters.  The problem i run into is when i try to call with null as a parameter.  I am using C# and ODP.NET to call the procedures.  </p>
  
  <p>Here's the head of the two procs:</p>
  
  <pre><code>PROCEDURE get_requests_with_files
  (
      o_results OUT sys_refcursor, 
      in_communicator IN VARCHAR2, 
      in_state        IN VARCHAR2  -- State of requests wanted (use NULL for ALL records)
  )
  </code></pre>
  
  <p>and</p>
  
  <pre><code>PROCEDURE get_requests_with_files
  (
       o_results OUT SYS_REFCURSOR,    
       in_communicator IN VARCHAR2,    
       in_states       IN flagTableType 
  )
  </code></pre>
  
  <p>The flagTypeTable is how i pass in a PLSQLAssociativeArray to my procedure and not really a part of the problem (i think).</p>
  
  <p>Here's the C# code i use to call the proc.</p>
  
  <pre><code>private static DataSet GetRequests(String consumer, List&lt;string&gt; states)
  {
      try
      {
          const string query = "TEST_PAK.get_requests_with_files";
  
          var retVal = new DataSet();
          var oComm = new OracleCommand(query, _oConn);
          var oDa = new OracleDataAdapter(oComm);
          oComm.CommandType = CommandType.StoredProcedure;
          oComm.CommandTimeout = CommandTimeout;
  
          //Parameters
  
          oComm.Parameters.Add("o_results", OracleDbType.RefCursor);
          oComm.Parameters["o_results"].Direction = ParameterDirection.Output;
  
          oComm.Parameters.Add("in_communicator", OracleDbType.Varchar2);
          oComm.Parameters["in_communicator"].Value = consumer;
  
          if (states.Count != 0)
          {
              oComm.Parameters.Add(new OracleParameter("in_states", OracleDbType.Varchar2)
              {
                  CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                  Value = states.ToArray()
              });
          }
          else
          {
              oComm.Parameters.Add("in_state", OracleDbType.Varchar2);
              oComm.Parameters["in_state"].Value = null;
          }
  
          _oConn.Open();
  
          oDa.Fill(retVal);
          return retVal;
      }
      catch (Exception ex)
      {
          Console.WriteLine(ex);
          return null;
      }
      finally
      {
          if (_oConn.State != ConnectionState.Closed) _oConn.Close();
      }
  }
  </code></pre>
  
  <p>I have tried placing colons in front of the parameters thinking the named parameters would do the trick with no luck.  </p>
  
  <p>I know i can call the code in SQLDeveloper like this:</p>
  
  <pre><code>variable o_results refcursor;
  execute Apps.Base_communicator.get_requests_with_files(:o_results, in_communicator =&gt; 'MYCOMM', in_state =&gt; null);
  print o_results;
  </code></pre>
  
  <p>So i know the overloads work, just not how to call them in c#</p>