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
   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
-Mathew Nolton


  • I agree. At a previous consultation we used the OracleProvider as opposed to Microsoft's and the OleDb provider. It was faster, but it had some other issues (it didn't always release cursors). However, at times you don't always have control over what provider you can use.

    -Mathew Nolton

  • can i use this sample in the same way to sql server and oracle.

    i mean, with oledb i can make an app web that it use so sql server or oracle in the same way.


  • Hugo,

    You can use the same "basic" code but you will need to differentiate the connection strings. However, you will find that the SqlClient is MUCH faster for connecting to SqlServer then OleDb is. Also, the Oracle Provider is a bit faster then OleDb for Oracle. The advantage of course is using the same basic code for different backends (e.g. not withstanding the connectionstring).

    -Mathew Nolton

  • Me salvó la vida !!!!!!!!!!!!!!

  • Can you please show an example with a procedure an VB.NET code?


  • prabhakar

    The change to do this is really in the connection string and not the code per se.

    also, i am not a person and anything i write without a compiler in front of me is bound to be wrong; however, try googling and get some code, then use the connection string described in this blog.


    -Mathew Nolton

  • Mathew & Co,

    I'm trying to run an intranet site setup on the same machine as the Oracle server and absolutely cannot correctly create a connection to Oracle 8x.

    I'm using .NET 2003 and no matter what type of connection I try and create (OLE, ODBC or Oracle) I get one or another error messages.

    Do you have to have an Oracle CLIENT installed on the host machine (as well as the SERVER) in order to connect to it? I get an error with a reference to a missing OCI.dll.

    Otherwise can ANYONE give me an example of a valid connection string for either of the following objects :

    - OLEDBConnection

    - OracleConnection

    - ODBCConnection

    Any help would be very much appreciated.


    Andrew Todd (Oracle Novice)

  • Andrew,

    If you are trying to connect to Oracle via an ASP.NET you also need to setup the NTFS permissions for the machine's ASPNET account (this is the ASP.NET worker process).

    The machine running the IIS server needs to have the Oracle client installed, and the ASPNET account needs the following permissions to the \oracle\ora92\bin folder:

    Read & Execute

    List Folder Contents



    Tim Cavanaugh

  • executing oracle sp through oracle connection

    namespace :;

    string sCon = "data source = ;uid=;pwd=";
    oracleconnection oCon = new oracleconnection(sCon);
    oraclecommand cmd = new oraclecommand();
    cmd.connection = oCon;
    cmd.commandText = "";
    cmd.commandType = commandtype.storedprocedure;
    foreach(parameter p in cmd.parameters)
    if(p.oracletype == oracletype.cursor) p.parameterdirection = parameterdirection.output;
    cmd.parameters[""].value = "";
    oracledataadapter da = new oracledataadapter(cmd);
    dataset dsout = new dataset("Cursor Output");

    finally you will get the cursor output in the dataset.

    good luck.


    Raja. K

Comments have been disabled for this content.