Some code for calling stored procs in c# with oracle.

Database.c#

using System;
using System.Collections.Generic;
using System.Text;
using System.Data; 
using Oracle.DataAccess.Client;
using System.Diagnostics;


namespace DataBase
{

    public interface ILogger
    {
        void LogCommandObject(string storeProcName, OracleCommand cmd);
    }


    interface ITransform<T>
    {
        bool Transform(StoredProcReturn spr,out T obj);
    }

    interface ITransformList<T>
    {
        IList<T> TransformList(StoredProcReturn spr);
    }

    public delegate void SetupCommand(OracleCommand command);

    public class StoredProcReturn
    {

        public const string RETURN_VALUE = "ReturnValue";

        public IDictionary<string,object> OutputParams { get; set;}
        public DataSet RefCursors { get; set;}
        public int ReturnValue { get; set; }

        public StoredProcReturn()
        {
            //For Testing
        }

        public StoredProcReturn(OracleCommand command, DataSet refCursors)
        {
            ReturnValue = (int)command.Parameters[RETURN_VALUE].Value;
            OutputParams = new Dictionary<string, object>();
            foreach (OracleParameter p in command.Parameters)
            {
                if (p.Direction != ParameterDirection.Output)
                {
                    continue;
                }
                //Convert from an Oracle String to a plain old string.
                if (p.OracleDbType == OracleDbType.Varchar2)
                {
                    p.Value = p.Value.ToString();
                }
                OutputParams.Add(p.ParameterName, p.Value);
            }
            RefCursors = refCursors;

        }


        public string GetError()
        {
            if(OutputParams.ContainsKey("PO_ERR_MSG"))
                return DBNullExtension.Coalesce<string>(OutputParams["PO_ERR_MSG"]);
            return "No Error Message recieved from Database";
        }
    }

    public class Database
    {

        private ILogger _logger;
        private string _connString;

        public Database(ILogger logger, string connString){
            _logger = logger;
            _connString = connString;
        }
        
       
        public StoredProcReturn Query(string name, SetupCommand sc)
        {
            DataSet data;
            StoredProcReturn output;
            using (OracleConnection connection = new OracleConnection(_connString))
            using (OracleCommand command = connection.CreateCommand())
            using (OracleDataAdapter adapter = new OracleDataAdapter(command))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = name;                
                
                sc(command);
                _logger.LogCommandObject(name, command);
                data = new DataSet();
                adapter.MapRefCursors();
                adapter.Fill(data);
                output = new StoredProcReturn(command, data);

            }            
            return output;
        }
    }


    public static class OracleExtensions
    {        
        public static void MapRefCursors(this OracleDataAdapter adapter)
        {
            int i = 0;
            foreach (OracleParameter param in adapter.SelectCommand.Parameters)
            {
                if (param.OracleDbType == OracleDbType.RefCursor)
                {
                    adapter.TableMappings.Add("Table" + (i == 0 ? "" : i.ToString()), param.ParameterName);
                    i++;
                }
            }
        }
    }
}

Database.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data; 
using Oracle.DataAccess.Client;
using System.Diagnostics;


namespace DataBase
{

    public interface ILogger
    {
        void LogCommandObject(string storeProcName, OracleCommand cmd);
    }


    interface ITransform<T>
    {
        bool Transform(StoredProcReturn spr,out T obj);
    }

    interface ITransformList<T>
    {
        IList<T> TransformList(StoredProcReturn spr);
    }

    public delegate void SetupCommand(OracleCommand command);

    public class StoredProcReturn
    {

        public const string RETURN_VALUE = "ReturnValue";

        public IDictionary<string,object> OutputParams { get; set;}
        public DataSet RefCursors { get; set;}
        public int ReturnValue { get; set; }

        public StoredProcReturn()
        {
            //For Testing
        }

        public StoredProcReturn(OracleCommand command, DataSet refCursors)
        {
            ReturnValue = (int)command.Parameters[RETURN_VALUE].Value;
            OutputParams = new Dictionary<string, object>();
            foreach (OracleParameter p in command.Parameters)
            {
                if (p.Direction != ParameterDirection.Output)
                {
                    continue;
                }
                //Convert from an Oracle String to a plain old string.
                if (p.OracleDbType == OracleDbType.Varchar2)
                {
                    p.Value = p.Value.ToString();
                }
                OutputParams.Add(p.ParameterName, p.Value);
            }
            RefCursors = refCursors;

        }


        public string GetError()
        {
            if(OutputParams.ContainsKey("PO_ERR_MSG"))
                return DBNullExtension.Coalesce<string>(OutputParams["PO_ERR_MSG"]);
            return "No Error Message recieved from Database";
        }
    }

    public class Database
    {

        private ILogger _logger;
        private string _connString;

        public Database(ILogger logger, string connString){
            _logger = logger;
            _connString = connString;
        }
        
       
        public StoredProcReturn Query(string name, SetupCommand sc)
        {
            DataSet data;
            StoredProcReturn output;
            using (OracleConnection connection = new OracleConnection(_connString))
            using (OracleCommand command = connection.CreateCommand())
            using (OracleDataAdapter adapter = new OracleDataAdapter(command))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = name;                
                
                sc(command);
                _logger.LogCommandObject(name, command);
                data = new DataSet();
                adapter.MapRefCursors();
                adapter.Fill(data);
                output = new StoredProcReturn(command, data);

            }            
            return output;
        }
    }


    public static class OracleExtensions
    {        
        public static void MapRefCursors(this OracleDataAdapter adapter)
        {
            int i = 0;
            foreach (OracleParameter param in adapter.SelectCommand.Parameters)
            {
                if (param.OracleDbType == OracleDbType.RefCursor)
                {
                    adapter.TableMappings.Add("Table" + (i == 0 ? "" : i.ToString()), param.ParameterName);
                    i++;
                }
            }
        }
    }
}