I’ve been working on a number of projects that required calling into an Oracle database to get various bits of data. Most of the information was related to product or customer in an ecommerce and/or ERP system. For the longest time, we’ve been relying on an old java listener that just sits there sends and receives sockets. No one is sure how it works, nor are they sure if they even have the latest source code. Everyone is scared that if we pull the latest from VSS and compile it that we’ll have a non-functioning system. They’re probably also wondering if it will even compile.

So here we are in the last few weeks of 2007 and we’re just starting to get going with .NET web services and .NET 2.0. Maybe we’ll even get rid of our old VB6 ecommerce platform (that’s a whole different story). So I found myself needing to create these web services that would call into Oracle and get what I needed. I searched a few places and didn’t find exactly what I needed, so I started with a little trial and error. My packages included CLOBs, so it was a little different. Here’s what I came up with. ProcedureInfo is just an object that holds information about the procedure that I’m calling and my connection to it.

        public static string SendDBRequest(ProcedureInfo pi)
        {
            OracleConnection con = GetPooledConnection(connectionString);
            OracleCommand cmd = con.CreateCommand();

            string clobString = pi.InMessage;

            byte[] clobContents = System.Text.ASCIIEncoding.Unicode.GetBytes(clobString);

            // Get our temporary clob
            OracleClob tempClob = new OracleClob(con);
            tempClob.Write(clobContents, 0, clobContents.Length);

            // Clear the parameters from the command object
            cmd.Parameters.Clear();

            // Set the name of the procedure
            cmd.CommandText = pi.Procedure;
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("result_", OracleDbType.Varchar2, 8000);
            cmd.Parameters[0].Direction = ParameterDirection.Output;
            cmd.Parameters[0].Value = string.Empty;

            cmd.Parameters.Add("result_text_", OracleDbType.Varchar2, 8000);
            cmd.Parameters[1].Direction = ParameterDirection.Output;
            cmd.Parameters[1].Value = string.Empty;

            cmd.Parameters.Add("out_message_", OracleDbType.Clob);
            cmd.Parameters[2].Direction = ParameterDirection.Output;

            // In params
            cmd.Parameters.Add("in_message_", OracleDbType.Clob);
            cmd.Parameters["in_message_"].Value = tempClob;
            cmd.Parameters["in_message_"].Direction = ParameterDirection.Input;

            cmd.Parameters.Add("command_", OracleDbType.Varchar2);
            cmd.Parameters["command_"].Value = pi.Command;
            cmd.Parameters["command_"].Direction = ParameterDirection.Input;

            cmd.ExecuteNonQuery();

            string detailText = cmd.Parameters["result_text_"].Value.ToString();

            // Get the response from the clob
            OracleClob rtrnClob;
            string clobMsg = string.Empty;

            try
            {
                rtrnClob = (OracleClob)cmd.Parameters["out_message_"].Value;
                clobMsg = rtrnClob.Value.ToString();
            }
            catch (Exception exc)
            {
                clobMsg = exc.Message;
            }

            cmd.Dispose();
            con.Dispose();

        }