Syntax error while trying to call Oracle package using ODBC in C #

Question

I am trying to use Oracle 9i package using ODBC and C #. I tried to use the syntax described in here , here , here and here , but I can not get it right.

Note : I am 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 call 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 get:

 Exception: ERROR [42000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement 

EDIT . I tested the package and code and it works in ODAC / ODP.NET, but I was asked to change it to ODBC for another server. The unpleasant part to me:

 OdbcCommand cmd = new OdbcCommand("{ BEGIN ? := CALL TESTUSER.TESTPKG.TESTFUNC(?,?,?,?); END; }", conn) 

Answer 1

Finally he earned. I added the size of each parameter and made corrections to the call: the function has four parameters (1 in, 3 outputs) and does not return a 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 useful: Using Oracle ODBC Drivers with Third-Party Products