dotNetChris @ Marisic.Net

April 10, 2008

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

Filed under: Programming — Tags: , , , , , — dotnetchris @ 10:17 am

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();

About these ads

10 Comments »

  1. Hi,

    Thanks for the useful article. It is of great help.
    Also, I was wondering if you have tried to retrive clob data from the DB and tried to display it on the browser.
    I have been trying to retrieve images stored as clobs on the DB and save it on the disk to be able to display it on the browser, but no succes yet. I have even tried doing Response.BinaryWrite() without success. Can you posibly shed some light on this issue too.

    Thanks in advance

    Comment by DotNetjunkie — April 14, 2008 @ 8:21 am

  2. This is fortunately much simpler than saving the data. All you need to do is pass the data into a regular old ref cursor and send it back to ASP.NET. I did a little bit of googling since I’ve never attempted to directly write binary of an image straight to a page and came across this after a few minutes I think combining the bringing the data back in a ref cursor and this article will help you alot.

    http://www.csharp-station.com/Articles/Thumbnails.aspx

    Another thing to keep in mind if you’re using the OracleClient as your DAL framework you can take advantage of

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

    And instead use it to .Read for

    // load the byte array with the image
    imageStream.Read(imageContent, 0, (int)imageStream.Length);

    And entirely bypass needing to pass the byte array around in buffers.

    Comment by dotnetchris — April 14, 2008 @ 4:43 pm

  3. Hi,

    Thanks for you reply. I did get to display the image finally. But the problems I was having was because I was trying to display an image loaded in the database using ADO and VB6.0. But when I uploaded an image using ADO.Net i was able to display back the image. But gain I am unable to display this image on the VB applicattion. In the VB app the image is being written from a char[] buffer (strings) into the clob. In .Net we write bytes. So i am still having trouble showing old data loaded in VB6 in ASP.Net.
    Any insights on this would be very helpful.

    Thanks again.

    Comment by DotNetjunkie — April 16, 2008 @ 1:57 am

  4. Did you try directly converting the char[] to a byte[] and then do the writing?

    Try something like this and let me know if that solves the final issue

    char[] adoData;
    ASCIIEncoding encoding = new ASCIIEncoding();
    byte[] array = encoding.GetBytes(adoData);

    Comment by dotnetchris — April 16, 2008 @ 11:43 am

  5. Yours solution is great but… how to close cursor opened in a procedure?

    Comment by blndn — May 6, 2009 @ 1:02 pm

  6. @blndn, Are you talking about my solution where you use a cursor as an out parameter? If you are, you can’t close it in the procedure because at the database level you have no idea whether it’s finished being used or not by the application which is why the application closes it with… hmm looking at my code I only close the connection and nothing specific to the oracle cursor maybe that’s why I had issues with oracle not actually closing the connection that it thought the cursor was still open. I’d play around with that theory but I don’t have access to an oracle database any longer.

    Comment by dotnetchris — May 6, 2009 @ 3:34 pm

  7. Great article – very helpful. Thanks.

    And although you do mention it, there is one item that I’d like to highlight, and that is the need for the CLOB column to be initialized with a value, whether that be with EMPTY_CLOB() or some other non NULL value. Without this, you’ll have NULL in the column when the .NET code tries to do its OracleLob.Write(byte[],int,int), and that will throw an unfortunately opaque exception:

    System.InvalidOperationException: Invalid operation. The connection is closed.

    I’ve read comments on other solutions on other forums that mention this specific “connection is closed” problem, but this is the only solution I’ve seen that pre-emptively answers it.

    @blndn: With regards to closing the open cursor, I believe Oracle provides guidance on SELECT… FOR UPDATE – it closes automatically when the transaction ends. There’s probably more explicit information elsewhere, as this citation makes more inferences than statements:

    http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10796/adlob_wo.htm#1008209

    Comment by Roehl Sioson — August 28, 2009 @ 1:56 pm

    • Good tip about the EMPTY_CLOB() garbage, just add it to the list of stupid things oracle does!

      Comment by dotnetchris — August 29, 2009 @ 2:42 pm

  8. Excellent one!!!. You saved me lot of time.

    Comment by Ravi — October 28, 2009 @ 10:39 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 242 other followers

%d bloggers like this: