Oracle OleDb Provider for .Net and Stored Procedures and Ref Cursors
For those of you working with Oracle and using the Oracle OleDb Provider for .Net. In other words, your connectionstring provider looks like Provider=OraOLEDB.Oracle;....... as opposed to Provider=MSDAORA;......
You probably have come across the issue of wanting to get a ref cursor back from a stored procedure. If you haven't already noticed, it doesn't work without some modifications to your connection string:
If your procedure has the following signature:
PROCEDURE
someProcedure(identifier in varchar,out_cursor out CURSOR_DEFINITION);
Then your connectionstring should look like this:
Provider=OraOLEDB.Oracle;OLEDB.NET=true;PLSQLRSet=true;Password=[password];User ID=[userid];Data Source=[DataSource]
Notice the following differences about this connectionstring
- PLSQLRSet=true This states that resultsets can be returned from stored procedures.
- OLEDBNet=True This means that OraOLEDB is compatible with the OLE DB .Net data provider. If you set this to true, it cannot be used with ADO.
Unfortunately, you cannot set these attributes at the object level like you could with ADO.
Your c# code, would look something like this:
using( OleDbConnection oracleConn = new OleDbConnection( _connectionString ) )
{
// open connection
try{oracleConn.Open();}
catch{throw new LogonException();}
// build command
OleDbCommand cmd = new OleDbCommand("{call someProcedure(?)}", oracleConn );
cmd.CommandType = CommandType.Text;
// add parameters
cmd.Parameters.Add( "identifier", OleDbType.VarChar ).Value = identifier;
OleDbDataAdapter da = new OleDbDataAdapter( cmd );
// fill dataset
DataSet ds = new DataSet();
da.Fill( ds);
// all done, return
return ds;
}
-Mathew Nolton