CODEWITHSUNDEEP
c#oracle-databasestored-procedures
Shawn
Shawn

Reputation: 11

Calling an Oracle Stored Procedure in C#

I am trying to call an Oracle stored procedure from a C# program. I am using a SYS_REFCURSOR an the output of the stored procedure. I am getting invalid SQL error when I reach the line

OracleDataReader reader = cmd.ExecuteReader()

in my C# program. I can't figure out why I am getting this invalid SQL error.

Here is the C# code:

private void button1_Click(object sender, EventArgs e)
{
        string custname;
        int custnbr;

        List<Customer> customers = new List<Customer>();

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

Here is the Oracle stored procedure:

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;

Upvotes: 1

Views: 4766

Answers (2)

MethodMan
MethodMan

Reputation: 18843

cmd.CommandType = CommandType.Text;

should be 

cmd.CommandType = CommandType.StoredProcedure;

Upvotes: 3

mason
mason

Reputation: 32694

As an alternative to MethodMan's answer, you should be able to keep the command type as Text, but change your SQL command to this:

cmd.CommandText = "BEGIN PROCEDURE_TEST END;";

MethodMan's method is better if you just need to call one procedure, but the way I did it above would allow you to do more procedures, so it's something to be aware of in the future.

Upvotes: 1

Related Questions