.NET中用OracleClient插入Clob大文本问题

.NET中用OracleClient插入Clob大文本问题

在Oracle中插入Clob数据时，当插入的数据<4K时，可以正常插入，但是>4K时，总是抱错：ORA-01704 文本太长。为了解决更大文本的插入，本人研究一下方法供参考，经测试没有问题。

 1private void button1_Click(object sender, System.EventArgs e)
 2        {
 3            // 源数据库
 4            OracleConnection wtgsConn = new OracleConnection("Data Source=wtgsktxx;User Id=wutan;Password=wutan;Integrated Security=no;");
 5            OracleCommand cmd = new OracleCommand("select wjbgwb,xmbh from awc32 where xmbh='5'",wtgsConn);
 6
 7            //目标数据库
 8            OracleConnection connect = new OracleConnection("Data Source=oratest;User Id=kttest;Password=test;Integrated Security=no;");
 9            OracleCommand lobCmd = connect.CreateCommand();
10            try
11            {
12                wtgsConn.Open();
13                connect.Open();
14                
15                // 取得 clob 字段
16                OracleDataReader dr = cmd.ExecuteReader();
17                dr.Read();
18                OracleLob readLob = ((OracleDataReader)dr).GetOracleLob(dr.GetOrdinal("wjbgwb"));
19                dr.Close();
20
21                // 为访问表定义一个游标 clobvar
22                string cmdSql = "DECLARE clobvar CLOB;";
23                cmdSql += " begin ";
24                cmdSql += " dbms_lob.createtemporary(clobvar, false, 0); :tempLob:= clobvar; ";
25                cmdSql += " end;";
26                lobCmd.CommandText = cmdSql;
27                lobCmd.Parameters.Add(new OracleParameter("tempLob", OracleType.Clob)).Direction = ParameterDirection.Output;
28                lobCmd.ExecuteNonQuery();
29                
30                // 利用事务处理（必须）
31                OracleTransaction tx = connect.BeginTransaction();
32                lobCmd.Transaction = tx;
33
34                // 定义一个临时变量
35                OracleLob tempLob = (OracleLob)lobCmd.Parameters["tempLob"].Value;
36                tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
37
38                // 将大字段内容写入临时变量
39                int BUFFER_LENGTH = 4000;
40                long i = BUFFER_LENGTH;
41                long startIndex = 0;
42                long length = 0;
43                byte[] Buffer = new byte[BUFFER_LENGTH];
44                while(i == BUFFER_LENGTH)
45                {
46                    i = readLob.Read(Buffer,0,BUFFER_LENGTH);
47                    length += i;
48                    startIndex += i;
49                    tempLob.Write(Buffer,0,(int)i);
50                }
51                tempLob.EndBatch();
52
53                // 给参数 tempLob 负值，并执行 Insert 语句
54                lobCmd.Parameters.Add(new OracleParameter("tempLob", OracleType.Clob)).Value = tempLob;
55                lobCmd.CommandText = "insert into test_clob(wjbgwb,xmbh) values(:tempLob,'5')";
56                lobCmd.ExecuteNonQuery();
57
58                // 提交事务
59                tx.Commit();
60
61                wtgsConn.Close();
62                connect.Close();
63            }
64            catch(Exception ex)
65            {
66                wtgsConn.Close();
67                connect.Close();
68                MessageBox.Show(ex.ToString());
69            }
70        }