Passing parameters to Oracle stored procedure?

[taohuang.tamu]

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 14LS-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.

 

[David Browne]

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;
. . .

First, the CommandType is wrong. It should be CommandType.Text. You have
supplied a complete PL/SQL block which happens to invoke a stored procedure.
CommandType.StoredProcedure is used when you specify only the procedure
name, and let the driver build the PL/SQL block for you.

Second, why are you using ODBC instead of System.Data.OracleClient?

Third, the PL/SQL you have supplied is invalid. Parameter markers must be
preceded by a ':' in Oracle.

odbcCommand.CommandText = "BEGIN myProcA_CNT, :B_CNT); END;";



David

 

[taohuang.tamu]

Hi David,
Thanks for your quick reply. It's using Odbc rather than
OracleClient because this code is unified code for both SQL Server and
Oracle.
I modified the commandType to be CommandType.Text and use "BEGIN
myProcA_CNT, :B_CNT); END;" as the CommandText, but still got exact
same error message. It looks like that it's complaining the symbol ":"
this time but the symbol shown in the error message is still upside
down question mark.

Tao

 

[David Browne]

Hi David,
Thanks for your quick reply. It's using Odbc rather than
OracleClient because this code is unified code for both SQL Server and
Oracle.

OK, but it's generally better to use the .NET generic providers or
Enterprise Library for this.

Generic Coding with the ADO.NET 2.0 Base Classes and Factories
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/vsgenerics.asp

Data Access Application Block
http://msdn.microsoft.com/library/d...ag2/html/entlibjan2006_dataaccessappblock.asp

I modified the commandType to be CommandType.Text and use "BEGIN
myProcA_CNT, :B_CNT); END;" as the CommandText, but still got exact
same error message. It looks like that it's complaining the symbol ":"
this time but the symbol shown in the error message is still upside
down question mark.

I never use ODBC, so I'm not sure what's really happening there.


David