Writting LOBs / CLOBs to oracle using C# ASP.NET 2.0

As a software developer sadly we are still bogged down by data access layers to legacy databases (Oracle 10g for this example). Now I’m sure many of you are questioning my calling oracle 10g a “legacy” system but with LINQ2SQL I am praying for the death of this behemoth forever. Until my day comes to laugh in the face of the oracle “gods” I am unfortunately forced to develop and support this beast.

Now if any of you have ever attempted to store XML or any other large object to oracle using a CLOB or LOB column I am sure you have encountered very high levels of frustration. I finally had the unfortunateness to be the next victim to this. Attempting to use Enterprise Library the way it’s intended to write to oracle with LOBs is just pretty much out of the question. So it comes down to either using the System.Data.OracleClient directly or using Oracle ODP.Net Oracle Client.

Searching for a solution to this problem pointed out very few solutions. The bulk of them require running dynamic sql straight from .NET to oracle. While this may work (I found this solution to only work haphazardly resulting in random oracle errors, highly suprising) many of us that are subjucated by oracle most likey have DBA policies that prevent this method from being at best, frowned upon. Trying to over come the limitation of not being able use oracle parameters to pass in the clob to a stored proc yet still need to avoid running dynamic sql was quite a challenge. I found nothing that specifically outlined my result.

Lets get down to work first lets get some basic assumptions.

Table XML_TBL— GUID— XML_CLOB — Input By/Date

USP_Insert_XML (p_guid in varchar2, p_inputby in varchar2, cur_OUT OUT ref_cursor)

AS

BEGIN

INSERT INTO XML_TBL(  GUID, XML_CLOB, INPUT_BY, INPUT_DATE)

VALUES ( p_guid, EMPTY_CLOB(), p_inputby, systime);

OPEN cur_OUT FOR

SELECT XML_CLOB

FROM XML_TBL

WHERE GUID = p_guid

FOR UPDATE;

END;

So pretty much a standard insert proc, EMPTY_CLOB() is basically just a fancy null since all LOB columns must be initialized during an insert. The magic is going to occur from the fact we are locking this row for update and passing back the reference to this table inside the ref cursor. Now to the .NET code

string connectionString = ConfigurationManager.ConnectionStrings[OracleConnectionString].ConnectionString;
byte[] clobByte = Encoding.Unicode.GetBytes(xmlStringToSave);

using (OracleConnection oracleConnection = new OracleConnection(connectionString))
{
oracleConnection.Open();
OracleTransaction transaction = oracleConnection.BeginTransaction();

OracleCommand oracleCommand = new OracleCommand(“USP_Insert_XML”, oracleConnection, transaction);
oracleCommand.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter(“p_guid”, OracleType.VarChar);
p1.Direction = ParameterDirection.Input;
p1.Value = guid;

OracleParameter p2 = new OracleParameter(“p_input_by”, OracleType.VarChar);
p2.Direction = ParameterDirection.Input;
p2.Value = “inputBy”;

OracleParameter p3 = new OracleParameter(“cur_OUT”, OracleType.Cursor);

p3.Direction = ParameterDirection.Output;
oracleCommand.Parameters.Add(p1)

oracleCommand.Parameters.Add(p2)

oracleCommand.Parameters.Add(p3)

OracleDataReader odr = oracleCommand.ExecuteReader();
odr.Read();

OracleLob clobColumn = odr.GetOracleLob(0);
clobColumn.Write(clobByte, 0, clobByte.Length);
transaction.Commit();

oracleConnection.Close();
}

So what we have done here is Open a Connection to Oracle. Build a new Transaction, during this transaction we will invoke a stored proc which returns a ref cursor to a table’s clob/Lob data column. Call read on the datareader, next we are going to get clob column from the data reader. In this example since the only column returned is the clob column it’s being accessed directly at column 0.  So make sure to change GetOracleLob(0) to either use GetOrdinal and the column name or put the correct column number in for yourself.

After we get the column we’re going to write to it as a stream (which is why we converted the xmlString to a byte array), call comit to save the xml or lob and close the connection.

I spent about 3 days researching this and could not find a clear example in one place on the interwebs so I hope this helps save you alot of time and headache. So in conclusion it’s possible to write an Oracle clob using only .NET code and stored procedures in this way, keep in mind for the writing to work successfully the oracle app user needs to have update (and possibly insert) access to this table, however they will not need to have select access for this or require the need to run dynamic sql straight from .NET

BloggingContext.ApplicationInstance.CompleteRequest();