而试图调用在C#中使用ODBC的Oracle软件包语法错误 [英] Syntax error while trying to call an Oracle package using ODBC in C#
问题描述
我试图消耗使用ODBC和C#的Oracle 9i的包。我试着用href="http://support.microsoft.com/kb/310130" rel="nofollow">这里,<一个here, <一href="http://stackoverflow.com/questions/2687187/what-is-the-proper-odbc-command-for-calling-oracle-stored-procedure-with-paramet">here和<一href="http://stackoverflow.com/questions/2799405/odbccommand-on-stored-procedure-parameter-not-supplied-error-on-output-param">here但我似乎无法得到它的权利。
注意的:我是不允许使用ODAC / ODP.NET在这种特殊情况下
这是包的结构:
定义
PARAM1数; - 在
参数2 VARCHAR2(200); --out
参数3 VARCHAR2(200); --out
PARAM4 VARCHAR2(200); --out
开始
PARAM1:= 123;
参数2:= NULL;
参数3:= NULL;
PARAM4:= NULL;
TESTUSER.TESTPKG.TESTFUNC(PARAM1,参数2,参数3,PARAM4);
DBMS_OUTPUT.PUT_LINE(参数2);
DBMS_OUTPUT.PUT_LINE(参数3);
DBMS_OUTPUT.PUT_LINE(PARAM4);
承诺;
结束;
这是我如何调用该软件包:
字符串VAR1 =123;
INT VAR2;
OdbcConnection CN =新OdbcConnection(驱动程序= {微软ODBC的Oracle};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TESTHOST)(PORT=1234))(CONNECT_DATA=(SID=TESTSID)));Uid=TESTUSER;Pwd=TESTPASS;");
cn.Open();
使用(的OdbcCommand CMD =新的OdbcCommand({BEGIN:?????= CALL TESTUSER.TESTPKG.TESTFUNC(,,,); END;},康涅狄格州))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText =TESTUSER.TESTPKG.TESTFUNC;
cmd.Parameters.Add(PARAM1,OdbcType.Decimal).Direction = System.Data.ParameterDirection.Input;
cmd.Parameters [PARAM1]值= VAR1。
cmd.Parameters.Add(参数2,OdbcType.VarChar).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(参数3,OdbcType.VarChar).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(PARAM4,OdbcType.VarChar).Direction = System.Data.ParameterDirection.Output;
cmd.ExecuteNonQuery();
int.TryParse(cmd.Parameters [参数2] Value.ToString(),出VAR2);
uAcctStatus = cmd.Parameters [参数3] Value.ToString()。
uReturnMsg = cmd.Parameters [PARAM4] Value.ToString()。
}
cn.Close();
返回VAR2;
这是错误消息我收到:
异常:错误[42000] [微软] [ODBC驱动程序用于Oracle] [甲骨文] ORA-00900:无效的SQL语句
修改:我已经测试包和code和工作在ODAC / ODP.NET,但我被要求更改为ODBC的另一台服务器。麻烦的一部分,对我来说是:
的OdbcCommand CMD =新的OdbcCommand({BEGIN:????= CALL TESTUSER.TESTPKG.TESTFUNC(,,, ?); END;},康涅狄格州)
终于得到它的工作。我添加的每个参数的大小,并提出改正的号召:该函数有四个参数(1,3出),并没有返回值:
使用(的OdbcCommand CMD = conn.CreateCommand())
{
cmd.CommandText ={CALL TESTUSER.TESTPKG.TESTFUNC(,,,????)};
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(PARAM1,OdbcType.Decimal,38).Direction = System.Data.ParameterDirection.Input;
cmd.Parameters [PARAM1]值= VAR1。
cmd.Parameters.Add(param2的,OdbcType.VarChar,5).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(参数3,OdbcType.VarChar,50).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(PARAM4,OdbcType.VarChar,200).Direction = System.Data.ParameterDirection.Output;
cmd.ExecuteNonQuery();
我也发现这个文件非常有用:使用与Oracle ODBC驱动程序第三方产品
I'm trying to consume an Oracle 9i package using ODBC and C#. I've tried to use the syntax described in here, here, here and here but I can't seem to get it right.
Note: I'm not allowed to use ODAC/ODP.NET in this particular case.
This is the package structure:
DECLARE
PARAM1 NUMBER; --in
PARAM2 VARCHAR2(200); --out
PARAM3 VARCHAR2(200); --out
PARAM4 VARCHAR2(200); --out
BEGIN
PARAM1 := 123;
PARAM2 := NULL;
PARAM3 := NULL;
PARAM4 := NULL;
TESTUSER.TESTPKG.TESTFUNC(PARAM1, PARAM2, PARAM3, PARAM4);
DBMS_OUTPUT.Put_Line(PARAM2);
DBMS_OUTPUT.Put_Line(PARAM3);
DBMS_OUTPUT.Put_Line(PARAM4);
COMMIT;
END;
This is how I'm calling the package:
string var1 = "123";
int var2;
OdbcConnection cn = new OdbcConnection("Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TESTHOST)(PORT=1234))(CONNECT_DATA=(SID=TESTSID)));Uid=TESTUSER;Pwd=TESTPASS;");
cn.Open();
using (OdbcCommand cmd = new OdbcCommand("{ BEGIN ? := CALL TESTUSER.TESTPKG.TESTFUNC(?,?,?,?); END; }", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "TESTUSER.TESTPKG.TESTFUNC";
cmd.Parameters.Add("PARAM1", OdbcType.Decimal).Direction = System.Data.ParameterDirection.Input;
cmd.Parameters["PARAM1"].Value = var1;
cmd.Parameters.Add("PARAM2", OdbcType.VarChar).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add("PARAM3", OdbcType.VarChar).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add("PARAM4", OdbcType.VarChar).Direction = System.Data.ParameterDirection.Output;
cmd.ExecuteNonQuery();
int.TryParse(cmd.Parameters["PARAM2"].Value.ToString(), out var2);
uAcctStatus = cmd.Parameters["PARAM3"].Value.ToString();
uReturnMsg = cmd.Parameters["PARAM4"].Value.ToString();
}
cn.Close();
return var2;
And this is the error message I'm receiving:
Exception: ERROR [42000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement
EDIT: I have tested the package and code and it works in ODAC/ODP.NET, but I was asked to change this to ODBC for another server. The troublesome part for me is:
OdbcCommand cmd = new OdbcCommand("{ BEGIN ? := CALL TESTUSER.TESTPKG.TESTFUNC(?,?,?,?); END; }", conn)
Finally got it to work. I added the size of each parameter and made corrections to the call: the function has four parameters (1 in, 3 out) and no return value:
using (OdbcCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "{ CALL TESTUSER.TESTPKG.TESTFUNC(?,?,?,?) }";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("PARAM1", OdbcType.Decimal, 38).Direction = System.Data.ParameterDirection.Input;
cmd.Parameters["PARAM1"].Value = var1;
cmd.Parameters.Add("PARAM2", OdbcType.VarChar, 5).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add("PARAM3", OdbcType.VarChar, 50).Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add("PARAM4", OdbcType.VarChar, 200).Direction = System.Data.ParameterDirection.Output;
cmd.ExecuteNonQuery();
I also found this document very helpful: Using the Oracle ODBC Drivers with Third Party Products
这篇关于而试图调用在C#中使用ODBC的Oracle软件包语法错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!