I am recently created an .NET Application that needs to utilize a Procedure from Oracle database. The Stored Procedure has five parameters. The first two is input, one is integer output and the last two is ref cursor output. Roughly, the SP looks like this:

PACKAGE PKG_TEST_PACKAGE AS
	TYPE REF_CURSOR IS REF CURSOR;
	PROCEDURE SP_TEST_PROCEDURE(strInput1 IN VARCHAR2, 
            intInput2 IN INTEGER, intOutput1 OUT INTEGER, 
            recordSet1 OUT REF_CURSOR, 
            recordSet2 OUT REF_CURSOR);
END;

After much tinkering and searching Internet, I found out that there are at least three ways to do it.

1. Using ADO.NET

Since Microsoft has deprecated System.Data.OracleClient, this option might not be good for production release.

using System;
using System.Data;
using System.Data.OracleClient;

namespace TestOracle
{
    class Program
    {
        static void Main(string[] args)
        {
            var con = new OracleConnection();
            con.ConnectionString = @"Server=(
                    DESCRIPTION=(
                        ADDRESS_LIST=(
                            ADDRESS=(PROTOCOL=TCP)
                            (HOST=YOUR_ORACLE_HOST_NAME)
                            (PORT=1521)
                        )
                    )
                    (CONNECT_DATA = (SID = YOUR_ORACLE_SID)));
                    Uid=YOUR_USERNAME;
                    pwd=YOUR_PASSWORD;";
            con.Open();
            var cmd = con.CreateCommand();
            cmd.CommandText = "PKG_TEST_PACKAGE.SP_TEST_PROCEDURE";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new OracleParameter
            {
                ParameterName = "strInput1",
                Direction = ParameterDirection.Input,
                Value = "SOME_VALUE"
            });
            cmd.Parameters.Add(new OracleParameter
            {
                ParameterName = "intInput2",
                Direction = ParameterDirection.Input,
                Value = 1903
            });
            cmd.Parameters.Add(new OracleParameter
            {
                ParameterName = "intOutput1",
                Direction = ParameterDirection.Output,
                OracleType = OracleType.Int32
            });
            cmd.Parameters.Add(new OracleParameter
            {
                ParameterName = "recordSet1",
                Direction = ParameterDirection.Output,
                OracleType = OracleType.Cursor
            });
            cmd.Parameters.Add(new OracleParameter
            {
                ParameterName = "recordSet2",
                Direction = ParameterDirection.Output,
                OracleType = OracleType.Cursor
            });
            cmd.ExecuteNonQuery();

            int intOutput1 = (int)cmd.Parameters["intOutput1"].Value;
            var recordSet1 = new DataTable();
            recordSet1.Load((OracleDataReader)cmd.Parameters["recordSet1"].Value);

            var recordSet2 = new DataTable();
            recordSet2.Load((OracleDataReader)cmd.Parameters["recordSet2"].Value);

            con.Close();
        }
    }
}

2. Using ODP.NET

If you’re developing in 64-bit environment, you might need to specifically set the running environment to x86 to prevent issues. For the time being, I think developing in x64 doesn’t worth the hassle needed just to get your environment working correctly.
Oh and BEWARE of the difference of Connection String between ADO.NET and ODP.NET

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace TestOracle
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                var con = new OracleConnection();
                con.ConnectionString = @"Data Source=(
                    DESCRIPTION=(
                        ADDRESS_LIST=(
                            ADDRESS=(PROTOCOL=TCP)
                            (HOST=YOUR_ORACLE_HOST_NAME)
                            (PORT=1521)
                        )
                    )
                    (CONNECT_DATA = (SID = YOUR_ORACLE_SID)));
                    User Id=YOUR_USERNAME;
                    Password=YOUR_PASSWORD;";
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "PKG_TEST_PACKAGE.SP_TEST_PROCEDURE";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new OracleParameter
                {
                   ParameterName = "strInput1",
                   Direction = ParameterDirection.Input,
                   Value = "SOME_VALUE"
                });
                cmd.Parameters.Add(new OracleParameter
                {
                   ParameterName = "intInput2",
                   Direction = ParameterDirection.Input,
                   Value = 1903
                });
                cmd.Parameters.Add(new OracleParameter
                {
                   ParameterName = "intOutput1",
                   Direction = ParameterDirection.Output,
                   OracleDbType = OracleDbType.Int32
                });
                cmd.Parameters.Add(new OracleParameter
                {
                   ParameterName = "recordSet1",
                   Direction = ParameterDirection.Output,
                   OracleDbType = OracleDbType.RefCursor
                });
                cmd.Parameters.Add(new OracleParameter
                {
                   ParameterName = "recordSet2",
                   Direction = ParameterDirection.Output,
                   OracleDbType = OracleDbType.RefCursor
                });   
                
                cmd.ExecuteNonQuery();

                var recordSet1 = new DataTable();
                OracleRefCursor ref1 = (OracleRefCursor)cmd.Parameters["recordSet1"].Value;
                recordSet1.Load(ref1.GetDataReader());

                var recordSet2 = new DataTable();
                OracleRefCursor ref2 = (OracleRefCursor)cmd.Parameters["recordSet2"].Value;
                recordSet2.Load(ref2.GetDataReader());

                con.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }
    }
}

3. Using Enterprise Library

I found out about this when I was working with a project that utilizes Subsonic to connect Oracle. When you are using ORM, you will likely to interact with classes from System.Data.Common. This is a problem because you can’t specify the data-type of the SP parameter. Luckily, this article saved me from confusion by giving me the needed pointer.

using System;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;

//..... Code Snipped
                //ORACLE_DB is connection string name found
                //in your App.config/web.config
                Database db = DatabaseFactory.CreateDatabase("ORACLE_DB");

                //Specify the number of SP's parameters
                object[] myParams = new object[5];
                
                //Database object will construct the reqired parameters
                DbCommand dbCmd = db.GetStoredProcCommand("PKG_TEST_PACKAGE.SP_TEST_PROCEDURE", myParams);
                db.SetParameterValue(dbCmd, "strInput1", "SOME_VALUE");
                db.SetParameterValue(dbCmd, "intInput2", 1903);

                //Tran is a transaction object, you can use this if 
                //you want to be part of a transaction, otherwise
                //you can remove this parameter
                db.ExecuteNonQuery(dbCmd, Tran);

                int intOutput1 = (int)dbCmd.Parameters["intOutput1"].Value;
                var recordSet1 = new DataTable();
                recordSet1.Load((DbDataReader)dbCmd.Parameters["recordSet1"].Value);

                var recordSet2 = new DataTable();
                recordSet2.Load((DbDataReader)dbCmd.Parameters["recordSet2"].Value);
//..... Code Snipped

I hope it helps 😉

1. 

   Reply

   Son said: 2016.07.26 16:33

   Thanks your post! It’s useful for me.