ASP .Net + Oracle Example for Beginner: Get the Resultset Using SELECT Statement (and Not Stored Procedure)

When I started working on a .Net application which uses Oracle database, I had a very simple requirement. I need to execute SELECT statement, get the resultset into the .Net and show the data in a grid view. Having a SQL Server background, I thought a quick search in google would provide me an example where I can imitate the logic and jump start the application development. When I searched online, all the examples required me to create a stored procedure and get the resultset. Having a read only access on the database and not wanting to add a stored procedure in the database, I had to pull my hair for couple of days to find what I see as a very simple example. Here is a pretty simple example which requires Oracle managed drivers to be installed on machine or a solution using Nuget package for Oracle.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Oracle.ManagedDataAccess.Types;

namespace OracleConnectivity {
 public partial class _Default: System.Web.UI.Page {
  protected void Page_Load(object sender, EventArgs e) {
   OracleCommand cmd = new OracleCommand();
   DataSet ds = null;
   using(OracleConnection con = new OracleConnection("DATA SOURCE=ORACLE_CONNECTION;USER ID=USERNAME;PASSWORD=PASSWORD;PERSIST SECURITY INFO=True;")) {
    cmd.Connection = con;
    cmd.CommandText = @ "
    BEGIN
    OPEN: cur1 FOR SELECT 'SUCCESS'
    AS Status, 'its good'
    as Message
    FROM DUAL;

    OPEN: cur2 FOR SELECT * from all_tables where rownum < 10;
    END;
    "; 
    cmd.CommandType = System.Data.CommandType.Text;
    OracleParameter param1 = new OracleParameter("cur1", OracleDbType.RefCursor, ParameterDirection.Output);
    cmd.Parameters.Add(param1);
    OracleParameter param2 = new OracleParameter("cur2", OracleDbType.RefCursor, ParameterDirection.Output);
    cmd.Parameters.Add(param2);
    using(OracleDataAdapter da = new OracleDataAdapter(cmd)) {
     ds = new DataSet();
     da.Fill(ds);
    }
    cmd.Dispose();
    con.Close();
   }
   gvDetails.DataSource = ds.Tables[0];
   gvDetails.DataBind();
   gvDetails0.DataSource = ds.Tables[1];
   gvDetails0.DataBind();
  }
 }
}

Explanation

sql statement contains two output cursors “cur1” and “cur2” which are defined as two oracle output parameters. Tables[0] in dataset refers to the output from “cur1” and Tables[1] in dataset refers to the output from “cur2” cursor. I am assigning these table data to the two separate gridviews. Both the grid views has “AutoGenerateColumns” = True.