title: Calling an Oracle Stored Procedure in C#
body: <p>I am trying to call an Oracle stored procedure from a C# program. I am using a <code>SYS_REFCURSOR</code> an the output of the stored procedure. I am getting invalid SQL error when I reach the line</p>
  
  <pre><code>OracleDataReader reader = cmd.ExecuteReader() 
  </code></pre>
  
  <p>in my C# program. I can't figure out why I am getting this invalid SQL error.  </p>
  
  <p>Here is the C# code:</p>
  
  <pre><code>private void button1_Click(object sender, EventArgs e)
  {
          string custname;
          int custnbr;
  
          List&lt;Customer&gt; customers = new List&lt;Customer&gt;();
  
          string oradb = "User Id=XXXXX;Password=XXXXX;Data Source=IP:PORT/xxxx;Pooling=false;";
          OracleConnection conn = new OracleConnection(oradb);
  
          try
          {
              conn.Open();
              OracleCommand cmd = new OracleCommand();
              cmd.Connection = conn;
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.CommandText = "PROCEDURE_TEST";
  
              OracleParameter oraP = new OracleParameter();
              oraP.ParameterName = "R_RECORDSET";
              oraP.OracleDbType = OracleDbType.RefCursor;
              oraP.Direction = System.Data.ParameterDirection.Output;
  
              cmd.Parameters.Add(oraP);
              cmd.CommandType = CommandType.Text;
              OracleDataReader reader = cmd.ExecuteReader();
  
              while (reader.Read())
              {
                  custnbr = reader.GetInt32(0);
                  custname = reader.GetString(1);
                  Customer custTemp = new Customer(custnbr, custname);
                  customers.Add(custTemp);
              }
  
              foreach (var cust in customers)
              {
                  textBox1.AppendText("Customer Number: " + cust.custnbr + "\t");
                  textBox1.AppendText("Customer Name: " + cust.custname + "\r\n");
              }
          }
  
          catch(Exception ex)
          {
              textBox1.AppendText(ex.ToString());
              conn.Close();
          }
      }
  </code></pre>
  
  <p>Here is the Oracle stored procedure:</p>
  
  <pre><code>create or replace PROCEDURE PROCEDURE_TEST 
  (   R_RECORDSET OUT SYS_REFCURSOR) AS 
  BEGIN
  OPEN R_RECORDSET FOR
  SELECT POTCHARGECATEGORY, POTCHARGECODE, POTCHARGEDESCRIPTION,
         POTCHARGEBASEAMT, SUM(POTCHARGEQTY), SUM(POTCHARGEAMOUNT)
  
  FROM riowner.ccum_customer customer
  
  WHERE ic.collection_Datetime =
  TO_DATE('30-SEP-2015 23:59:59','DD-MON-YYYY HH24:MI:SS')
  
  GROUP BY POTCHARGECATEGORY, POTCHARGECODE, POTCHARGEDESCRIPTION,
           POTCHARGEBASEAMT;
  END PROCEDURE_TEST;
  </code></pre>