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
Content Delivery API – The Case of the Duplicate API Refresh Token

Creating a custom refresh provider to resolve the issues with duplicate tokens in the DXC The post Content Delivery API – The Case of the Duplicate...

David Lewis | Sep 29, 2022 | Syndicated blog

New Optimizely certifications - register for beta testing before November 1st

In January 2023, Optimizely is making updates to the current versions of our certification exams to make sure that each exam covers the necessary...

Jamilia Buzurukova | Sep 28, 2022

Optimizely community meetup - Sept 29 (virtual + Melbourne)

Super excited to be presenting this Thursday the 29th of September at the Optimizely community meetup. For the full details and RSVP's see the...

Ynze | Sep 27, 2022 | Syndicated blog

Preview multiple Visitor Groups directly while browsing your Optimizely site

Visitor groups are great - it's an easy way to add personalization towards market segments to your site. But it does come with it's own set of...

Allan Thraen | Sep 26, 2022 | Syndicated blog