.NET

Calling Oracle stored procedures and functions in C#

Even if you are a SQL Server and /or .NET developer you will find yourself every now and then in a situation, where you have to deal with other databases, for instance Oracle. You might have to, as I do, load data into Oracle Financial Applications, which is an absolute pain in the a… (I will write more about that as soon as the trauma subsides). And as such you might find yourself in the situation that you have to call Oracle stored procedures from your C#/.NET code.

This is, on paper, no big deal. It work’s on large parts exactly like working with SQL Server stored procedures. You can use

System.Data.OleDb

if this is your preferred provider, but actually you should use

System.Data.OracleClient

So far, so good, but there are some things you need to be aware of.

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

namespace ClassLibrary1
{
    public class Class1
    {
        public int CallOracleProcedure()
        {
            // create Oracle connection
            OracleConnection con = new OracleConnection("{myOracleConnectionString}");
            OracleCommand comm = new OracleCommand(); // create command
            int retVal = -1;

            try
            {
                con.Open(); // open connection
                comm.Connection = con; // add connection to command
                comm.CommandText = "FND_GLOBAL.APPS_INITIALIZE"; // name of the Oracle procedure
                comm.CommandType = CommandType.StoredProcedure; // set type to stored procedure

                // add input parameters
                comm.Parameters.Add(new OracleParameter("inputVal1", 123));
                comm.Parameters.Add(new OracleParameter("inputVal2", "test"));
                // if necessary set data type
                comm.Parameters.Add(new OracleParameter("inputDate", DateTime.Now));
                comm.Parameters["inputDate"].DbType = OracleType.Datetime;

                // add output parameter
                comm.Parameters.Add(new OracleParameter("outputVal1", OleDbType.Integer));
                comm.Parameters["outputVal1"].Direction = ParameterDirection.Output;

                comm.ExecuteNonQuery(); // execute procedure

                retVal = (int)comm.Parameters["outputVal1"].Value;  // read return value
          }
          finally
          {
                con.Close();
          }

            return retVal;
        }

    }
}

As you can see in the code above, it’s quite simple and the same as in SQL Server. What you need to take care of, are the different data types. Depending on how well the OleDb provider is written, you can have some problems with dates or numbers. OracleClient provider gives you the Oracle data types, presumably, but still, dates are always a pain.
You also need to be aware of the difference between function and stored procedure:

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

namespace ClassLibrary1
{
    public class Class1
    {
        public int CallOracleFunction()
        {
            // create Oracle connection
            OracleConnection con = new OracleConnection("{myOracleConnectionString}");
            OracleCommand comm = new OracleCommand(); // create command
            int retVal = -1;

            try
            {
                con.Open(); // open connection
                comm.Connection = con; // add connection to command
                comm.CommandText = "myOracleFunction"; // name of the Oracle procedure
                comm.CommandType = CommandType.StoredProcedure; // set type to stored procedure

                // add input parameters
                comm.Parameters.Add(new OracleParameter("inputVal1", 123));
                comm.Parameters.Add(new OracleParameter("inputVal2", "test"));
                // if necessary set data type
                comm.Parameters.Add(new OracleParameter("inputDate", DateTime.Now));
                comm.Parameters["inputDate"].DbType = OracleType.Date;

                // add return parameter
                comm.Parameters.Add(new OracleParameter("returnVal", OracleType.Integer));
                comm.Parameters["returnVal"].Direction = ParameterDirection.ReturnValue;

                comm.ExecuteNonQuery(); // execute procedure

                retVal = (int)comm.Parameters["returnVal"].Value; // read return value
            }
            finally
            {
                con.Close();
            }

            return retVal;
        }

    }
}

As you can see, the code looks almost the same. You even have to set the command type to ‘stored procedure’ to execute a function. But if you were to execute the above code for a stored procedure instead of a function, you would get this exception:

 ORA-06550: line 1, column 25:
    PLS-00222: no function with name 'APPS_INITIALIZE' exists in this scope
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

The main and only difference (from a C# perspective) between Oracle Function and Oracle Stored Procedure is the return value. Functions have return values, procedures have output parameters. And if you pass a return-parameter to a stored procedure, it throws an exception.

%d bloggers like this: