Re: Calling Oracle stored procedure in C#???

taohuang.tamu_at_gmail.com wrote:
> 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 [HY000]
> [Oracle][ODBC][Ora]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.

Get rid of the @ signs. IOW, change

odbcCommand.Parameters.Add("@A_CNT", OdbcType.Int);

...to...

odbcCommand.Parameters.Add("A_CNT", OdbcType.Int);

Regards,

Steve Received on Mon Jul 17 2006 - 12:43:35 CDT