title: c# return resultset from Oracle store procedure and fill a DataTable
body: <p>I'm a c# SQL Server developer <strong>new to Oracle programming</strong>. The code below works for setting cmdText to: "select * from myTable". I'd like to now put this in a stored procedure, <strong>I need help with how to write this Oracle stored procedure and get the results into a DataTable in my c# code</strong>. Thanks. Code:</p>
  
  <pre><code>private DbProviderFactory DbFactory
      {
          get
          {
              dbProviderFactory = dbProviderFactory  ?? DbProviderFactories.GetFactory(providerInvariantName);
              return dbProviderFactory;
          }
      }
  
  public DataTable ExecDataTable(string cmdText, params IDataParameter[] cmdParams)
      {
          DataTable resultDT = new DataTable();
  
          using (DbConnection dbConn = DbFactory.CreateConnection())
          {
              dbConn.ConnectionString = connectionString;
              using (DbCommand dbCmd = DbFactory.CreateCommand())
              {
                  dbCmd.CommandText = cmdText;
                  dbCmd.Connection = dbConn;
  
                  try
                  {
                      dbConn.Open();
  
                      if (cmdParams != null)
                      {
                          dbCmd.Parameters.AddRange(cmdParams);
                      }
  
                      using (DbDataAdapter dbDA = DbFactory.CreateDataAdapter())
                      {
                          dbDA.SelectCommand = dbCmd;
                          dbDA.Fill(resultDT);
                      }
                  }
                  finally
                  {
                      dbConn.Close();
                  }
              }
          }
          return resultDT;
      }
  </code></pre>
  
  <p>Note: connectionString, providerInvariantName are set previously in code.</p>
  
  <p>Also <strong>any advice on refactoring my code is appreciated</strong> this is the approach I've taken to support ODP.net and also ODBC connections to Oracle which is required on site.</p>
  
  <p><strong>Update</strong></p>
  
  <p>I can get this to working using:</p>
  
  <pre><code>new Oracle.DataAccess.Client.OracleParameter("result", Oracle.DataAccess.Client.OracleDbType.RefCursor, ParameterDirection.Output);
  </code></pre>
  
  <p>However I can't seem to get a generic solution to work using <strong>DbParameter or IDataParameter</strong> how do I do this to <strong>support ODBC and ODP.net</strong> ?</p>