(原) ODP.NET 演示基于行锁定的 LOB 更新

using System;
using System.Collections.Generic;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace Sample66
{
    // 演示基于行锁定的 LOB 更新
    class Program
    {
        static void Main(string[] args)
        {
            // Connect
            string constr = "User Id=scott;Password=tiger;Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.24)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = bjoracle.oracle10g.mynet)))";
            OracleConnection conn = Connect(constr);

            if (conn == null)
                return;

            // Setup
            Setup(conn);

            // Transaction
            OracleTransaction txn = conn.BeginTransaction();
            OracleCommand cmd = new OracleCommand("", conn);

            try
            {
                // Select the lob with the primary key
                cmd.CommandText = "select STORY, THEKEY from multimedia_tab where THEKEY=1";

                OracleDataReader reader = cmd.ExecuteReader();
                reader.Read();
                OracleClob clob = reader.GetOracleClobForUpdate(0);  // Lock the row
                Console.WriteLine("Old Data: {0}", clob.Value);

                // Modify the CLOB column of the row
                string ending = "The end";
                clob.Append(ending.ToCharArray(), 0, ending.Length);

                // Release the lock
                txn.Commit();

                // Fetch the new data; transaction or locking not required
                cmd.CommandText = "select STORY from multimedia_tab where THEKEY =1";
                reader = cmd.ExecuteReader();
                reader.Read();
                clob = reader.GetOracleClob(0);
                Console.WriteLine("New Data: {0}", clob.Value);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }
            finally
            {
                // Dispose oraclecommand object
                cmd.Dispose();

                // Close and Dispose OracleConnection object
                conn.Close();
                conn.Dispose();
            }



            Console.ReadLine();
        }

        // Opening a new Connection
        public static OracleConnection Connect(string connectStr)
        {
            OracleConnection conn = new OracleConnection(connectStr);
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
                conn = null;
            }
            return conn;
        }

        // Setup the necessary Tables & Test Data
        public static void Setup(OracleConnection conn)
        {
            StringBuilder blr;
            OracleCommand cmd = new OracleCommand("", conn);

            blr = new StringBuilder();
            blr.Append("DROP TABLE multimedia_tab");
            cmd.CommandText = blr.ToString();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Warning: {0}", ex.Message);
            }

            blr = new StringBuilder();
            blr.Append("CREATE TABLE multimedia_tab(thekey NUMBER(4) PRIMARY KEY,");
            blr.Append("story CLOB, sound BLOB)");
            cmd.CommandText = blr.ToString();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }

            blr = new StringBuilder();
            blr.Append("INSERT INTO multimedia_tab values(");
            blr.Append("1,");
            blr.Append("'This is a long story.Once upon a time',");
            blr.Append("'898787787874454654564578978971114544897')");
            cmd.CommandText = blr.ToString();

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }
        }
    }
}

注意：
1、创建OracleTransaction对象
2、创建OracleDataReader对象，并调用Read()方法
3、锁定行： OracleClob clob = reader.GetOracleClobForUpdate(0);
4、更新行： clob.Append(ending.ToCharArray(), 0, ending.Length);
5、提交事务，并释放锁 txn.Commit();

引用：ODP.NET SAMPLE