在C#中调用Oracle存储过程

本文关键字:Oracle 存储过程 调用 | 更新日期: 2023-09-27 18:19:46

我正试图从C#程序调用Oracle存储过程。我正在使用SYS_REFCURSOR作为存储过程的输出。当我到达行时,我得到了无效的SQL错误

OracleDataReader reader = cmd.ExecuteReader() 

在我的C#程序中。我不明白为什么会出现这个无效的SQL错误。

这是C#代码:

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

以下是Oracle存储过程:

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;

在C#中调用Oracle存储过程

cmd.CommandType = CommandType.Text; 

应该是

cmd.CommandType = CommandType.StoredProcedure;

作为MethodMan答案的替代方案,您应该能够将命令类型保持为Text,但将SQL命令更改为:

cmd.CommandText = "BEGIN PROCEDURE_TEST END;";

如果你只需要调用一个过程,MethodMan的方法会更好,但我上面做的方式会允许你做更多的过程,所以这是未来需要注意的。