Writing to Oracle CLOB fields using System.Data.OracleClient - shoot me down please
If you're a .NET coder and does database stuff against Oracle, this is your chance to put things right :) Keep reading and add comments please!
In one of our current projects we need to write large chunks of text to an Oracle CLOB field from an ASP.NET 1.1 application. We've been using the standard Microsoft System.Data.OracleClient libraries so far (to write smaller amounts of data) and it has worked out quite well for us. I didn't want to introduce another library pack like the Oracle ODP.NET even though the later versions of that pack may work very well.
In older Java projects I've been writing and reading CLOBS and I know you have to handle those fields a bit different than normal VARCHARs and such. So I sat down and started to search the .NET Dynamic Help and whatever I could find on the Internet, and the funny thing is I found three different ways of doing it; 2 really simple and one not so simple. The weird thing is that I don't think the first two options is supposed to be working - but when I test them they work just fine. I'll publish the 3 ways here, and hopefully someone who knows what he's doing with .NET and Oracle can shoot me down in flames. I just want to do the right thing here.
To test this, I have created a simple table in Oracle, which consists of 2 fields; ID (INT) and TEXT (CLOB). That's it. I'm reading a simple text-file created with Notepad, which is 499 KB in size.
NOTE: This is just test-code, nothing you'd want to have in production. If you decide to use some of this code, please add proper error, exception and transaction handling to it.
So, first way of doing it using an OracleDataAdapter, is (if I understand the Class Library docs) not supposed to be working. But it does:
public void writeDataWithDA()
{
FileInfo fi = new FileInfo("c:/temp/testfile.txt");
StreamReader sr = new StreamReader(fi.FullName);
String clob = sr.ReadToEnd();
sr.Close();
OracleDataAdapter da = new OracleDataAdapter("SELECT ID, TEXT FROM CLOBTEST",ConnectionString);
DataTable dt = new DataTable();
// get the schema
da.FillSchema(dt, SchemaType.Source);
OracleCommandBuilder cb = new OracleCommandBuilder(da);
int id = 2;
// create a row containing the data
DataRow row = dt.NewRow();
row["ID"] = id;
row["TEXT"] = clob;
dt.Rows.Add(row);
// update the table
da.Update(dt);
}
The second way of doing it uses an OracleCommand and is also not supposed to be working (I think), but it does:
public void writeDataWithCommand()
{
FileInfo fi = new FileInfo("c:/temp/testfile.txt");
StreamReader sr = new StreamReader(fi.FullName);
String tempBuff = sr.ReadToEnd();
sr.Close();
using(OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
Console.WriteLine("Connected...") ;
String strSQL = "INSERT INTO CLOBTEST (ID,TEXT) VALUES (1,:TEXT_DATA) ";
OracleParameter parmData = new OracleParameter();
parmData.Direction = ParameterDirection.Input;
parmData.OracleType = OracleType.Clob;
parmData.ParameterName = "TEXT_DATA";
parmData.Value = tempBuff;
OracleCommand cm = new OracleCommand();
cm.Connection = conn;
cm.Parameters.Add(parmData);
cm.CommandText = strSQL;
cm.ExecuteNonQuery();
conn.Close();
}
Console.WriteLine("Done!") ;
}
Now comes the third way of doing it. This is how the .NET Class Library documentation describes C/BLOB handling, by creating a temporary LOB object in Oracle and then write to that object before inserting in into the table. This also works fine, but it requires a bit more hassle with transactions and stuff:
public void writeWithTempBlob()
{
FileInfo fi = new FileInfo("c:/temp/testfile.txt");
StreamReader sr = new StreamReader(fi.FullName);
String tempBuff = sr.ReadToEnd();
sr.Close();
using(OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
Console.WriteLine("Connected...") ;
OracleTransaction tx = conn.BeginTransaction();
OracleCommand tempcmd = conn.CreateCommand();
tempcmd.Transaction = tx;
tempcmd.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;";
tempcmd.Parameters.Add(new OracleParameter("tempclob",
OracleType.Clob)).Direction = ParameterDirection.Output;
tempcmd.ExecuteNonQuery();
//get the temp lob object
OracleLob tempLob = (OracleLob)tempcmd.Parameters[0].Value;
//transform into byte array
System.Text.Encoding enc = Encoding.Unicode; //MUST be unicode encoded!
Byte[] b = enc.GetBytes(tempBuff);
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(b,0,b.Length);
tempLob.EndBatch();
OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = tx;
cmd.CommandText = "INSERT INTO CLOBTEST (ID, TEXT) VALUES (:ID, :TEXT)";
cmd.Parameters.Add("ID", 3);
cmd.Parameters.Add("TEXT", OracleType.Clob).Value = tempLob; //insert the temp lob
cmd.ExecuteNonQuery();
tx.Commit();
}
Console.WriteLine("Done!") ;
}
Note that to write to the temporary CLOB, the text must be unicode or you will just get a mess of characters written :)
So, since all three ways seems to be working just fine for me, what is the right way of doing this? Load your gun and fire away...