2

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)
Community
  • 1
  • 1
Atanas Mendes
  • 75
  • 1
  • 3
  • 13
  • Are you sure `TestFunc` is a function, not a procedure? The PL/SQL call seems to indicate it is a procedure, not a function, in which case there is no return value. – Justin Cave Sep 16 '13 at 21:29
  • @JustinCave I guess you are right. This is just one of the several syntax combinations I've tried to no avail. I understand it is a function which receives four parameters and returns one value. – Atanas Mendes Sep 16 '13 at 21:36
  • 1
    If that were the case, your PL/SQL block should error out because you have no variable to store the result that would be returned from the function. You can't call a function in PL/SQL without assigning the return value to something. – Justin Cave Sep 16 '13 at 21:41
  • @JustinCave I want to store the return value in var2; I successfully did this using ODP.NET in another environment, but in this server I can only use ODBC – Atanas Mendes Sep 16 '13 at 21:50
  • Initially, I'm just looking at the PL/SQL block you posted to call the procedure/ function. That PL/SQL block is calling a procedure (there is no return value). If that works, then you must be trying to call a procedure. If the PL/SQL block you posted is throwing an error, then it makes sense that you really have a function because the PL/SQL block is not attempting to assign the return value of the function to anything. – Justin Cave Sep 16 '13 at 21:53
  • 1
    I think you are confusing the ODBC call syntax. remove the BEGIN..CALL... ; END; pieces or leave that in and remove the ODBC escape characters - {}. So, try "BEGIN ? := TESTUSER.TESTPKG.TESTFUNC(?,?,?,?); END;" or try "{ ? := CALL TESTUSER.TESTPKG.TESTFUNC(?,?,?,?) }" – OldProgrammer Sep 16 '13 at 23:49
  • Thanks @OldProgrammer, your comment made me realize a dumb mistake - I wasn't waiting for a return value. – Atanas Mendes Sep 18 '13 at 21:05

1 Answers1

3

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

Atanas Mendes
  • 75
  • 1
  • 3
  • 13