Monthly Archives: February 2012

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.

Generating random numbers in T-SQL

Every once in a while the necessity arises to generate some random numbers in your T-SQL code, be it for some random selection of price winners, be it to randomize a sort order or whatever reason.
SQL Server provides a built-in function rand() which returns a random number. Great, you might say, until you try it.

select rand()

This returns a float value between 0 and 1. To make that useful you have to tweak it. I chose to create a function that returns a random integer in between the boundaries I need.

-- view that returns a random number
begin try
    exec('create view dbo.vw_RandNumber as select 1');
end try begin catch end catch
go

alter view dbo.vw_RandNumber
as
    -- return a random number using the built-in rand() function
    select
        rand() as RandNumber    -- returns a float value between 0 and 1
    ;
go

-- function to return a random integer value
begin try
    exec('create function dbo.fn_RandNumber() returns int');
end try begin catch end catch
go

alter function dbo.fn_RandNumber
    (@Min int   -- lower boundary of desired random value
    ,@Max int)  -- upper boundary of desired random value
    returns int
AS
begin
    declare
         @rand float    -- the built-in rand() function returns a float,
                        -- so we need one here
        ,@ret int       -- our return value
    ;

    while @ret is null  -- just to be sure, we really return sth valid
    begin
        -- rand() returns a float between 0 and 1, so we need to tweak it
        -- to get a random number between our boundaries
        -- f.i. @Min = 1, @Max = 100 => random number between 1 and 100
        select      @rand = @Min
                            + (select RandNumber
                            from dbo.vw_RandNumber)
                            * (@Max-@Min)
        ;
        -- now we convert the float into an integer
        -- simply by assigning it to an int variable, thus cutting of the decimals
        -- Note: this is still an implicit conversion, even if no convert() or
        --       cast() is used meaning it still needs the same resources
        select @ret = @rand   -- 94,56873936663979 => 94
    end
    -- return the result
    return @ret;
end
go

This way I can get a random number within any range I want (as long as it’s integer).

select dbo.fn_RandNumber(1, 100)
select dbo.fn_RandNumber(1, 500000)
select dbo.fn_RandNumber(800, 900)
select dbo.fn_RandNumber(-50, 0)

You might ask why we need this construct with a view. Simple answer, the usage of rand() is not allowed within a user defined function. It will throw an error message:
Invalid use of a side-effecting operator ‘rand’ within a function.

Therefore we need to ‘fool’ SQL Server by creating a view that returns just the one random number and then select from the view within the function.

Caveats

There are two major caveats to consider:

Firstly, rand() returns a float value, we also need a float variable in the function. Using floats is never really a good idea. Normal CPUs are not optimized for floating point calculations, meaning that operations with floats need additional CPU cycles. Unless you have an additional floating point processor in your SQL Server machine (which is very unlikely), this will take up some CPU time.

There are other ways to create random numbers, you’ll find them when you google. Here is a neat example by Jeff Moden.

select
     abs(checksum(newid())) % (@Max - @Min + 1) + 1 as randNum
    ,col1
    ,col2
    ,col3
FROM
    dbo.someTable

I prefer the construct with the view and the function, since I think it’s quite flexible (I can have functions for decimal, money, whatever), easy to read in stored procedure code and easy to maintain.

%d bloggers like this: