What is the proper odbc command for calling Oracle stored procedure with parameters from .Net?

Question

In the case of MSFT SQL Server 08, it is:

odbcCommand = new OdbcCommand("{call " + SP_NAME + " (?,?,?,?,?,?,?) }", odbcConn);

When I try to do the same thing for Oracle, I get:

OdbcException: ERROR [HYC00] [Oracle][ODBC]Optional feature not implemented.

Feel free to ask for clarification, and please help. I am using .Net 3.5, SQL Server 08, and Oracle 11g_home1.

P.S. The Oracle stored procedure does have some 3 more parameters, but I believe I am handling this in my code.

Answer 1

I'm running .NET 3.5 and Oracle 10gR2. I've added an output parameter in response to your comment.

Server, Uid, and Pwd have been changed to protect the innocent. Set them to appropriate values.

My stored procedure:

create or replace
procedure test_proc(p1 in number, p2 in out varchar2) is
begin
  p2 := to_char(p1 + to_number(p2));
end;

My test code:

using System;
using System.Data;
using System.Data.Odbc;

class OdbcTest
{
    const string connectionString =
        @"Driver={Microsoft ODBC for Oracle};" +
        @"Server=MyTnsName;" +
        @"Uid=MySchema;" +
        @"Pwd=MyPassword;";

    public static string TryMe()
    {
        using (var odbcConn = new OdbcConnection(connectionString))
        using (var odbcCommand = odbcConn.CreateCommand())
        {
            odbcCommand.CommandText = "{ CALL test_proc(?, ?) }";
            odbcCommand.CommandType = CommandType.StoredProcedure;

            odbcCommand.Parameters.Add("p1", OdbcType.Decimal).Value = 42;
            var p2 = odbcCommand.Parameters.Add("p2", OdbcType.VarChar, 30);
            p2.Direction = ParameterDirection.InputOutput;
            p2.Value = "25";

            odbcConn.Open();
            odbcCommand.ExecuteNonQuery();

            return p2.Value as string; // returns 67
        }
    }
}

When using OUT or IN OUT parameters, the Size property of the OdbcParameter must be set to an adequate value.

In response to your comment regarding exception handling, I would have the caller of the data access method handle exceptions. With the using construct, the Dispose method will be called automatically on the command and connection objects whether there is an exception or not.