OleDb.NET访问返回结果集的Oracle存储过程问题...
hz890 2009-01-22 06:36:42 OleDb.NET访问返回结果集的Oracle存储过程问题...
存储过程TestPackage1.GetData1需要从表test返回结果集:
CREATE OR REPLACE package STD.TestPackage1 is
type mycursor is ref cursor;
procedure GetData1(ta varchar2,ret_cursor out mycursor);
end TestPackage1;
/
CREATE OR REPLACE package body STD.TestPackage1 is
procedure GetData1(ta varchar2,ret_cursor out mycursor) is
sqlstr varchar2 (4000);
begin
if ta='' then
open ret_cursor for select * from test;
else
sqlstr:='select * from test where A=:a';
open ret_cursor for sqlstr using ta;
end if;
end GetData1;
end TestPackage1;
/
test表很简单,只有两个字段:
A VARCHAR2(200) NOT NULL,
NUM NUMBER(38) DEFAULT 0 NOT NULL
---------------------------------------------------------------------------------------
ASP.NET(C#)访问程序:
public DataTable GetDataTable()
{
DataTable dt = new DataTable();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand("TestPackage1.GetData1", conn);
cmd.CommandType = CommandType.StoredProcedure;
OleDbParameter para = new OleDbParameter();
para.ParameterName = "ta";
para.Value = "a";
para.OleDbType = OleDbType.Variant;
para.Direction = ParameterDirection.Input;
cmd.Parameters.Add(para);
OleDbParameter para1 = new OleDbParameter();
para1.ParameterName = "ret_cursor";
para1.Value = DBNull.Value;
para1.OleDbType = OleDbType.Variant;
para1.Direction = ParameterDirection.Output;
cmd.Parameters.Add(para1);
OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
sda.Fill(dt);
}
return dt;
}
在执行至sda.Fill(dt);时报错:ORA-01036: 變數名稱?號碼無效(公司用的是VS2005繁体中文版)
不知当如何解决。我知道如果采用OracleClient而不是OleDb访问,程序是不会有问题的,但现在用户要求采用OleDb来实现对Oracle的访问,不知当如何解决,请各位不要再要求我用OracleClient来解决此问题,感谢各位支持!