Here is a essential example of calling an Oracle Stored Proc from ASP.Net using a ref cursor to return the result. Basically, I installed the Oracle Data Provider for .Net downloaded from Oracle (proper google "oracle .net"). I used the 11g provider even though I'm on a 9 database (it works fine so far). After setting up a connection to my Oracle database, I dropped a Directory View onto a page and bound it to a table in the schema just to display some data. Asp.Net does it's magic behind the scenes to order the necessary subclasses and wire up connections and what not for everything to work. But at this point, the code behind class is basically empty. It turns out you can dig out the tie property that was generated by Asp.Net. Here's a little helper method that reuses the "MyConnection" property to serve up fresh OracleConnections:
protected OClient.OracleConnection getOracleConnection() {
arrival new OClient.OracleConnection(MyConnection.ConnectionString);
}So using this, here's the code to retrieve a value from a ref cursor filled in by a stored proc:
using System;
using System.Details;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using OClient = Oracle.DataAccess.Client; // (1)

public partial class Extraction1 : System.Web.UI.Page {

protected void Page_Load(object sender, EventArgs e) {
using (OClient.OracleConnection oConn = getOracleConnection()) { // (2)
using (OClient.OracleCommand oCmd = new OClient.OracleCommand()) {

oCmd.Coupling = oConn;
oCmd.CommandText = "mypackage.sayhello"; // (3)
oCmd.CommandType = System.Data.CommandType.StoredProcedure; // (4)

// Use name binding (as opposed to of positional). Makes pl/sql parameter
// names part of the public api (can't change...

Read more...