Rom's Rants: HOWTO: Write to an Oracle NCLOB using .NET

Friday, January 18, 2008

HOWTO: Write to an Oracle NCLOB using .NET

Note: These instructions are for if you are using the Microsoft-provided provider for Oracle for .NET, not ODP.NET.

You have a blank NCLOB field in your Oracle database, but any time you try to save more than 32Kb of data to it, you get an error about an invalid conversion. The solution is a bit hackish, but here goes:

using (OracleConnection conn = 
 new OracleConnection(connectionString))
{
 conn.Open();

 OracleTransaction tx = conn.BeginTransaction();

 OracleCommand cmdLob = new OracleCommand(
  String.Format("SELECT NCLOB_FIELD FROM MYTABLE WHERE " +
                "PK = {0} FOR UPDATE", pk), conn, tx);
 cmdLob.CommandType = CommandType.Text;
 using (OracleDataReader dr = cmdLob.ExecuteReader())
 {
   while (dr.Read())
   {
     OracleLob lob = dr.GetOracleLob(0);
     lob.Seek(0, System.IO.SeekOrigin.Begin);
     lob.Write(System.Text.Encoding.Unicode.GetBytes(textToSave), 0, 
      textToSave.Length * 2);
   }
  }

  tx.Commit();
  conn.Close();
}

A quick walkthrough of the code...
Open your connection to the Oracle database.
Start a transaction on that connection.
Get an OracleDataReader that points to the NCLOB field. Make sure that you suffix your query with FOR UPDATE, or you won't be able to update your CLOB.
Get the LOB object from the OracleDataReader.
At that point, the Lob object is essentially a stream, but to write your string, you need to write it in a format that Oracle won't complain about, hence getting the Unicode-encoded bytestream for the text.
Commit the transaction.
Close the connection.

posted by Michael Russell at 1/18/2008 01:26:00 PM