Gonzalo Gonzalez
Mar 15, 2010
(0 votes)

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:

// 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 =
OracleLob tempLob = (OracleLob)cmd.Parameters[0].Value;

System.Text.UnicodeEncoding Unicode = new System.Text.UnicodeEncoding();
byte[] tempbuff = Unicode.GetBytes(paramValue);
tempLob.Write(tempbuff, 0, tempbuff.Length);
param =
new OracleParameter(ProviderSpecificParameterName(parameterName), OracleType.Clob);
((OracleParameter)param).Value = tempLob;
if (localTransaction)

 E.g Creation of a Blob Parameter:

if (serializedData.Length < 32000)
CreateParameter(cmd, "p_ItemData", OracleType.Blob, serializedData);
// 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;

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


OracleParameter param =
new OracleParameter("p_ItemData", OracleType.Blob);
((OracleParameter)param).Value = tempLob;

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;


for Clob:
param = new OracleParameter(ProviderSpecificParameterName(parameterName),

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 Oracle.DataAccess  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:)


Mar 15, 2010


Please login to comment.
Latest blogs
Optimizely SendGrid SMTP host is deprecated

SendGrid is a services for sending email that is included in Optimizely DXP. Previously smtp.episerver.net was the recommended SMTP server to use,...

Tomas Hensrud Gulla | Dec 4, 2022 | Syndicated blog

Hosting Optimizely CMS 12 on Docker Engine

Since Optimizely CMS can now be deployed as a Docker container, here is a demonstration of building, running and scaling an Optimizely CMS 12 site ...

Stefan Holm Olsen | Dec 4, 2022 | Syndicated blog

How to use CacheTagHelper with content areas in Optimizely CMS 12

I might be going out on a limb here - if you have a better solution, feel very free to share it!  Upgrading your Optimizely web application from .N...

Andreas J | Dec 2, 2022

The 1001st Piece in your 1000 Piece Puzzle: .NET Default Interface Functions

I was recently working with a client who wanted a reasonably large subsystem added to Optimizely that would add automated management to their...

Greg J | Nov 28, 2022 | Syndicated blog