我试图通过我的C＃代码调用带有输出参数的存储过程作为sys_refcursor。但它总是给我错误说“ORA-01036：非法变量名称/号码” [英] I am trying to call a store procedure with output parameter as sys_refcursor through my C# code. But it is constinously giving me error saying "ORA-01036: illegal variable name/number"

问题描述

我试图通过我的C＃代码调用带有输出参数的存储过程作为sys_refcursor。但它不断给我错误说ORA-01036：非法变量名称/号码



我使用.NET framework 2.0和System.Data.OracleClient进行连接Oracle数据库。



有什么我想念的吗？



提前致谢



我尝试过：

I am trying to call a store procedure with output parameter as sys_refcursor through my C# code. But it is continuously giving me error saying "ORA-01036: illegal variable name/number"

I am using .NET framework 2.0 and System.Data.OracleClient for connecting Oracle database.

Is there anything that I am missing?

Thanks in advance

What I have tried:

I am writing here the C# and SP which I am using.

C# Code:

Public DataTable ExecuteReport(ReportFilters filterAttribute)
        {
            DataTable dtDetailedReport = new DataTable();
            using (OracleConnection conn = new OracleConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                OracleCommand cmd = new OracleCommand();
                cmd.Connection =conn;
                cmd.CommandText = "count_emp_by_dept";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("pin_no", OracleType.VarChar, 200).Value = filterAttribute.PIN;
                cmd.Parameters.Add("pResult ", OracleType.Cursor).Direction = ParameterDirection.Output;
                OracleDataAdapter adap =new OracleDataAdapter(cmd);
                adap.Fill(dtDetailedReport);
                conn.Close();
            }
            return dtDetailedReport;
        }

我正在使用的SP

SP that I am using

Ceate or replace procedure count_emp_by_dept(pin_no Varchar2, pResult out sys_refcursor)
is
begin
  open pResult for 
 select pinname from TOC2_Test  where pin = pin_no;
end count_emp_by_dept;

推荐答案

请按照：asp.net - 使用OracleCommand获取oracle输出参数 - Stack Overflow [ ^ ]

Follow this: asp.net - Get oracle output parameter using OracleCommand - Stack Overflow[^]

引用：

确保在执行前在参数上设置SIZE属性。对于Oracle中的输出参数，指定的大小充当缓冲区。如果未设置缓冲区，则为0，因此您无法从数据库中获取值。

Make sure you set the SIZE property on the parameter before executing. With output parameters in Oracle, the specified size acts as a buffer. If the buffer isn't set, it is 0 so you don't get the value from the database.

var param = Ocmd.Parameters.AddWithValue("OUTPUTParam","").Direction = ParameterDirection.Output;
param.Size = 255;

这篇关于我试图通过我的C＃代码调用带有输出参数的存储过程作为sys_refcursor。但它总是给我错误说“ORA-01036：非法变量名称/号码”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！