executing Oracle stored procedures using ADO (c#)

Question

Im trying to call a Oracle stored proc from a C# application using the following code

Connection conn = new Connection();
Recordset rs = new Recordset();
conn.Open("Provider=MSDAORA;User Id=username;Password=password;Data Source=DB;", null, null, 0); ;
rs.Open("sproc 'abc', 'xyz'", conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, -1);

where abc and xyz are input parameters..

However, I get "invalid SQL statement exception" when I try to run it..

Is there any other way to execute a oracle stored proc. I can execute MSSQL stored procs or normal Oracle queries in the same way described above..

I even tried using createparameter, but that didn't help either

Thanks, Sam

Answer 1

Grab the Oracle ODP.NET tools: http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

They are what I use to interact with our Oracle database from my ASP.NET application

Check here for an example of calling an Oracle stored procedure in C#.

Basically, with the package:

// Create oracle command object for the stored procedure
OracleCommand cmd = new OracleCommand("HR_DATA.GETCURSORS", conn);
cmd.CommandType = CommandType.StoredProcedure;

// Enter a parameter for the procedure
OracleParameter dep_id = new OracleParameter();
dep_id.OracleDbType = OracleDbType.Decimal;
dep_id.Direction = ParameterDirection.Input;
dep_id.Value = 60;
cmd.Parameters.Add(dep_id);

// Add more parameters ... 

// Execute the stored procedure

Here's a link to the API documentation

Answer 2

Nevermind.. Apparently I was missing brackets around input parameters...

Thanks, Sam