calling Oracle stored procedure in C#???

Visual Basic .NET » Database Programming

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

-- Cerebrus --

Please correct me if I'm wrong, but shouldn't the CommandText property refer to the name of the Stored procedure, instead of SQL statements,
when you set the CommandType property to "StoredProcedure" ? If by any chance I'm right, then your commandText would simply be : "myProc"

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

an example:

OracleConnection LoOracleConnection;
OracleCommand LoOracleCommand;
LoOracleConnection = new OracleConnection("The connection string");
LoOracleCommand = new OracleCommand();
LoOracleCommand.CommandText = "Stored Procedure name";
LoOracleCommand.CommandType = CommandType.StoredProcedure;
//.Net Framework 1.1 LoOracleCommand.Parameters.Add("@Parameter1" ,OracleType.Int32);
//.Net Framework 2.0 LoOracleCommand.Parameters.AddWithValue("@Parameter1", 0);
LoOracleConnection.Open();
LoOracleCommand.Connection = LoOracleConnection;
try
{
LoOracleCommand.ExecuteNonQuery();
}
catch (Exception PoException)
{
throw PoException;
}
finally
{
LoOracleConnection.Close();
}

-- Laresuco --

Sorry, I forget:

LoOracleCommand.Parameters

.Value = 0;
LoOracleCommand.Parameters

.Direction =
ParameterDirection.InputOutput;

Hope this can help you.

-- Cerebrus --

Ah ! Thanks, Peter. Was beginning to have serious doubts there... ;-)

-- Tito --

Ok, it has been a good bit since I last played with ODBC but it should be similar to SQL connections (what I have dealing with way too much).
I also have not touched Oracle (I know, shame on me) But generally when you define parameters, you want to create the parameter, set the value, and then add it. For example:

(sorry, this is for SQL)
SqlCommand cmd = new SqlCommand(...all the needed data here...);
SqlParameter param = new SqlParameter();
param.Name = "@ParameterName";
param.Value = "the value";
cmd.Parameters.Add(param);
(you can repeat the last four lines for addional parameters)
cmd.ExecuteReader();

BTW...create a dll class that you can use across multiple projects...it will save you work and time.

-- BradleyPeter --

IFAIK you are correct. The OP will also have to add the parameters to the command, I believe.
Peter

-----Original Message-----
From: DotNetDevelopment

Re: calling Oracle stored procedure in C#???
Please correct me if I'm wrong, but shouldn't the CommandText property refer to the name of the Stored procedure, instead of SQL statements,
when you set the CommandType property to "StoredProcedure" ? If by any chance I'm right, then your commandText would simply be : "myProc"

Home