从 .net 中的 Oracle 存储过程获取标量值
我有一个返回 1 或 0 的存储过程。我需要在调用它的 .net 函数中返回该值。我知道如何从 Oracle 获取数据集（使用 refCursor），我知道如何将数据输入 Oracle，调用 cmd.ExecuteNonQuery() （假设您正在使用存储过程来输入数据）。但是如何获得存储过程的单个返回值呢？我没有返回数据集，而是返回 1 或 0。所以这有点不同。
谢谢。
更新：代码更具体地显示我哪里出错了？
try
{
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ACODBConnectionString3"].ConnectionString))
{
using (OracleCommand cmd = new OracleCommand(sProc,conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter inputParm = new OracleParameter("HospitalFinIn", OracleDbType.Varchar2);
inputParm.Value = HosFin;
//inputParm3.Value = Double.Parse(isActive);
OracleParameter outRefParam = new OracleParameter("cur_out", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
//outRefParam.
cmd.Parameters.Add(inputParm);
cmd.Parameters.Add(outRefParam);
outcome = (int)cmd.ExecuteScalar();
if (outcome == 1)
FinUniq = false;
else if (outcome == 0)
FinUniq = true;
else
FinUniq = true;
//outcome = cmd.ExecuteNonQuery();
//adapter = new OracleDataAdapter(cmd);
//adapter.Fill(ds);
}
}
}
这是我运行此程序时遇到的异常，
Specified cast is not valid.
不确定我做错了什么。 存储过程肯定会返回 0 或 1。
I have a stored proc that return's either a 1 or 0. I need this value back in the .net function that calls it. I know how to get a data set back from Oracle (use a refCursor), I know how to input data into Oracle, call cmd.ExecuteNonQuery() (assuming you are using a stored proc to input data). But how do you get a stored proc single return value? I am not returning a Data set but a 1 or a 0. So this is a little bit different.
Thanks.
UPDATE: CODE TO more specifically show where I am going wrong?
try
{
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ACODBConnectionString3"].ConnectionString))
{
using (OracleCommand cmd = new OracleCommand(sProc,conn))
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter inputParm = new OracleParameter("HospitalFinIn", OracleDbType.Varchar2);
inputParm.Value = HosFin;
//inputParm3.Value = Double.Parse(isActive);
OracleParameter outRefParam = new OracleParameter("cur_out", OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);
//outRefParam.
cmd.Parameters.Add(inputParm);
cmd.Parameters.Add(outRefParam);
outcome = (int)cmd.ExecuteScalar();
if (outcome == 1)
FinUniq = false;
else if (outcome == 0)
FinUniq = true;
else
FinUniq = true;
//outcome = cmd.ExecuteNonQuery();
//adapter = new OracleDataAdapter(cmd);
//adapter.Fill(ds);
}
}
}
This is the exception I get when I run this
Specified cast is not valid.
Not sure what I am doing wrong.
The stored proc definitely returns a 0 or 1.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您是否使用类似于
OPEN curInt FOR select 42 from Dual的内容通过 OUT REFCURSOR 返回标量值？您只需使用“OracleCommand.ExecuteScalar()”执行存储过程即可获取标量值。请记住为 OUT refcursor 创建一个 OracleParameter，并将方向设置为“ParameterDirection.Output”，将“OracleDbType”属性设置为“OracleDbType.RefCursor”
Are you returning the scalar value via an OUT REFCURSOR using something similar to
OPEN curInt FOR select 42 from dual?You can obtain the scalar value by just executing the stored procedure using 'OracleCommand.ExecuteScalar()'. Remember to create an OracleParameter for the OUT refcursor and set is direction to 'ParameterDirection.Output' and the 'OracleDbType' property to 'OracleDbType.RefCursor'