Oracle table getting locked when trying to write out a clob

Question

I'm writing out formatted text to a CLOB in Oracle table. Eventually as the process runs the table will get locked. When our DBA checks out the connections it appears that I've created multiple locks on the table and there are no other connections from other users. Any ideas on why the code below would eventually create locks on the table? It normally takes a few days of this code running a couple 100 times a day before the lock is created. There appear to be no hanging transactions.

public void Update_Html_Out(string key, string shortTitle, string htmlText)
{
    byte[] newvalue = Encoding.Unicode.GetBytes(htmlText);

    string sql = "UPDATE html_out SET short_title = :short_title, actual_text = :clob WHERE key = :key";

    using (var conn = new OracleConnection(_connectionString))
    using (var cmd = new OracleCommand(sql, conn))
    {
        conn.Open();

        using (var transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted))
        {
            cmd.Transaction = transaction;
            using (var clob = new OracleClob(conn))
            {
                clob.Write(newvalue, 0, newvalue.Length);

                cmd.Parameters.Add("short_title", shortTitle);
                cmd.Parameters.Add("clob", clob);
                cmd.Parameters.Add("key", key);

                cmd.ExecuteNonQuery();

                transaction.Commit();
            }
        }
    }
}

Answer 1

Try this code. Note, you need to save CLOB, not BLOB (you said it)

public void Update_Html_Out(string key, string shortTitle, string htmlText)
{
    string sql = @"UPDATE html_out SET 
                     short_title = :short_title, 
                     actual_text = :clob 
                   WHERE key = :key";

    using (var conn = new OracleConnection(_connectionString))
    {
        conn.Open();

        using (var transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted))
        {
            using (var cmd = new OracleCommand(sql, conn))
            {
                cmd.Transaction = transaction;
                cmd.Parameters.Add("short_title", OracleDbType.Varchar2, shortTitle, ParameterDirection.Input);
                cmd.Parameters.Add("clob", OracleDbType.Clob, htmlText, ParameterDirection.Input);
                cmd.Parameters.Add("key", OracleDbType.Varchar2, key, ParameterDirection.Input);

                cmd.ExecuteNonQuery();
            }
            transaction.Commit();
        }
    }
}