calling parameterized Oracle stored procedure in C#????

Visual Basic .NET » Database Programming

calling parameterized Oracle stored procedure in C#???? -- emeraldth --

I'm trying to call a parameterized stored procedure in C# using microsoft ODBC .NET. Could you please give me some help or hint? Thank you very much!
C# code:
... //connection code odbcCommand.CommandType = CommandType.StoredProcedure;
odbcCommand.CommandText = "BEGIN myProc(?, ?); END;";
odbcCommand.CommandTimeout = 0;

OdbcParameter paramA =
odbcCommand.Parameters.Add("@A_CNT", OdbcType.Int);
paramA.Value = _a_cnt;
OdbcParameter paramB =
odbcCommand.Parameters.Add("@B_CNT", OdbcType.Int);
paramB.Value = _b_cnt;

try
{
odbcCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.Error.WriteLine(DateTime.Now.ToString()
+ ex.Message);
throw ex;
}

The SP itself looks like:
CREATE OR REPLACE PROCEDURE myProc (
A_CNT IN BINARY_INTEGER,
B_CNT IN BINARY_INTEGER) AS BEGIN NULL;
COMMIT;
END;

I tried to directly use odbcCommand.CommandText = "BEGIN myProc(1, 2); END;";
And that works! However, unfortunately I actually need to use the two variables _a_cnt and _b_cnt as input arguments for the SP. Then I got following error message which complains about the question mark in
"BEGIN myProc(?, ?); END;"

ERROR

ORA-06550: line 1, column 14:PLS-00103: Encountered the symbol
"" when expecting one of the following: ( ) - + case mod new not null others
<an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current exists max min prior sql stddev sum variance execute multiset the both leading trailing forall merge year month DAY_ hour minute second timezone_hour timezone_minute timezone_region timezone_abbr time timestamp interval date
<a

The suspicious symbol in the error message is a question mark upside down.

Oracle 10g release 2.

-- Laresuco --

The namespace System.Data.OracleClient has all the objects you need to connect and call stored procedures. And in the property CommmandText you just have to put the name of the stored procedure, this assuming that the stored procedure it's already in the database.

Home