Oracle 函数不是过程或未定义。忽略语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23411434/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle function is not a procedure or is undefined. Statement ignored
提问by merp
I am trying to call an Oracle function from C# that returns multiple rows but it is not working. Here is the function I am using:
我正在尝试从 C# 调用一个返回多行的 Oracle 函数，但它不起作用。这是我正在使用的功能：
create or replace function return_columns(
tableName IN varchar
)
return types.ref_c
as
c_result types.ref_c;
begin
open c_result for
select column_name
from all_tab_columns
where table_name = tableName;
return c_result;
end return_columns;
Here is the type:
这是类型：
create or replace package types
as
type ref_c is ref cursor;
end;
I am in C# code calling the function like this:
我在 C# 代码中调用这样的函数：
OracleConnection oraConn = new OracleConnection("DATA SOURCE=MySource;PASSWORD=MyPassword;USER ID=MyID");
OracleCommand objCmd = new OracleCommand("MyID.RETURN_COLUMNS", oraConn);
objCmd.CommandType = CommandType.StoredProcedure;
OracleParameter oraParam = new OracleParameter("tableName", OracleType.VarChar);
oraParam.Value = "MY_TABLE";
oraCmd.Parameters.Add(oraParam);
oraConn .Open();
DataTable dt = new DataTable();
OracleDataAdapter ad = new OracleDataAdapter(objCmd);
ad.Fill(dt);
oraConn.Close();
And it keeps returning this error:
它不断返回此错误：
'RETURN_COLUMNS' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored
'RETURN_COLUMNS' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored
What is wrong with my Oracle function?
我的 Oracle 函数有什么问题？
采纳答案by Nick Krasnov
You simply need to define one more parameter, a parameter responsible for return value. Here is an example:
你只需要再定义一个参数，一个负责返回值的参数。下面是一个例子：
OracleParameter retVal = new OracleParameter("retVal", OracleDbType.RefCursor);
retVal.Direction = ParameterDirection.ReturnValue;
Note #1:The retValparameter should be added first in the parameter list, otherwise you might receive ORA-00306: wrong number or type of arguments..error.
注意#1：该retVal参数应首先添加到参数列表中，否则您可能会收到ORA-00306: wrong number or type of arguments..错误消息。
cmd.Parameters.Add(retVal); -- ReturnValue parameter is being added first
cmd.Parameters.Add(tabName); -- then goes everything else
Note #2:It would be better to use ODP for .NETinstead of obsolete and deprecated Microsoft Oracle client (System.Data.OracleClient)
注意 #2：最好将ODP 用于 .NET而不是过时和不推荐使用的 Microsoft Oracle 客户端 ( System.Data.OracleClient)
Note #3:Use varchar2data type instead of varcharin your PL/SQL code. As of now they are synonyms but their behavior might change in the future.
注意 #3：使用varchar2数据类型而不是varchar在您的 PL/SQL 代码中。截至目前，它们是同义词，但它们的行为在未来可能会发生变化。
回答by Mike
It's been a while, but this should get you moving in the right direction...
已经有一段时间了，但这应该会让你朝着正确的方向前进......
OracleParameter prm = cmd.CreateParameter();
prm.OracleDbType = OracleDbType.RefCursor;
prm.ParameterName = "retcurse";
prm.Direction = ParameterDirection.ReturnValue;
oraCmd.Parameters.Add(prm);
OracleRefCursor rc = (OracleRefCursor)prm.Value;
// Not sure about this next part off the top of my head...
OracleDataAdapter ad = new OracleDataAdapter(objCmd);
DataSet ds = new DataSet();
ad.Fill(ds, "retcurse", rc);
// May also be this
// ad.Fill(ds, "retcurse", (OracleRefCursor)(oraCmd.Parameters["retcurse"].Value));
