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

OracleTransaction tx = conn.BeginTransaction();

OracleCommand cmdLob = new OracleCommand(
"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);

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.

1 comment:

Bill said...

You're still developing new code using Oracle? Crap I thought I had you guys moving towards MS.