EPiServer CMS6 on Oracle moving on to ODP.NET

On 15th June 2009, Microsoft annouced their decision to deprecate their System.Data.OracleClient assembly. As an EPiServer developer working with Oracle, you’ll probably know that EPiServer CMS 5 uses this assembly. We decided to change direction in EPiServer CMS 6 and use the ODP.NET ADO.NET provider from the Oracle Corporation.

In recent years, ODP.NET has added lots of new features,  such as performance tuning, user-defined types, advanced queuing, RAC connection pooling, and supporting multiple ODP.NET client versions simultaneously on the same machine.

Time to move on!

Below you can find some “ugly” peaces of code done with the previous data provider, due to differences between .NET CLR Types and Oracle data types:

E.g. Creation of a Clob parameter:

try
{
    // WARNING! This is the ugliest piece of code I have ever written... 
    //  not for the faint-of-heart.
    // Believe it or not, but this is the way to handle large data
    //   fields in Oracle :-(
    cmd.CommandText = "declare xx clob; 
                       begin dbms_lob.createtemporary(xx, false, 0);
                      :tempblob := xx; end;";
    cmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Clob)).Direction = 
                                                    ParameterDirection.Output;
    cmd.ExecuteNonQuery();
    OracleLob tempLob = (OracleLob)cmd.Parameters[0].Value;

    
    System.Text.UnicodeEncoding Unicode = new System.Text.UnicodeEncoding();
    byte[] tempbuff = Unicode.GetBytes(paramValue);
    tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
    tempLob.Write(tempbuff, 0, tempbuff.Length);
    tempLob.EndBatch();
    param = 
    new OracleParameter(ProviderSpecificParameterName(parameterName), OracleType.Clob);
    ((OracleParameter)param).Value = tempLob;
    if (localTransaction)
    {
        cmd.Transaction.Commit();
    }
}

 

 E.g Creation of a Blob Parameter:

 

if (serializedData.Length < 32000)
{
    CreateParameter(cmd, "p_ItemData", OracleType.Blob, serializedData);
}
else
{
    // WARNING! This is the ugliest piece of code I have ever copied.
    OracleCommand tmpCmd = CreateCommand("declare xx blob; begin 
    dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;");
    tmpCmd.CommandType = CommandType.Text;
    tmpCmd.Parameters.Add(new OracleParameter(
       "tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
    tmpCmd.ExecuteNonQuery();

    OracleLob tempLob = (OracleLob)tmpCmd.Parameters[0].Value;
    tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
    tempLob.Write(serializedData, 0, serializedData.Length);
    tempLob.EndBatch();

    tmpCmd.Parameters.Clear();
    tmpCmd.Dispose();

    OracleParameter param = 
    new OracleParameter("p_ItemData", OracleType.Blob);
    ((OracleParameter)param).Value = tempLob;
    cmd.Parameters.Add(param);
}

 

 

 

 

What does it look like now?

 

for Blob:

CreateParameter(cmd, "p_ItemData", OracleDbType.Blob, serializedData);

 

Internally CreateParameter create  an Oracle Parameter associated to the command(cmd):

 

private static void CreateParameter(OracleCommand command,
                                    String parameterName,
                                    OracleDbType parameterType,
                                    Object parameterValue,
                                    ParameterDirection parameterDirection)
        {
            OracleParameter param = command.CreateParameter();
            param.ParameterName = parameterName;
            param.OracleDbType = parameterType;
            param.Value = parameterValue;
            param.Direction = parameterDirection;

            command.Parameters.Add(param);
        }

 

for Clob:

param = new OracleParameter(ProviderSpecificParameterName(parameterName),
OracleDbType.Clob);

Much better!!!

 

There are some misconceptions to clarify:      

Misconception: "I have to download a particular version of ODP.NET depending on the version of my database."
Fact: Any version of ODP.NET works with any version of Oracle Database.

Misconception: "EPiServer is compiled against ODAC 11.1.0.6.21( Oracle.DataAccess 2.111.6.0.)  I'm in big trouble!"
Fact: Multiple versions of ODP.NET(Oracle Clients) can live on the same box and the application can target whatever specific one you need. You can of course also use assembly redirects.

Misconception: "This means I have to pay for an Oracle provider now!"
Fact: ODP.NET is free!

You’ll find more information in the ODP.NET for Microsoft  OracleClient Developers webpage.

Happy coding:)