.NET中用OracleClient插入Clob大文本问题
.NET中用OracleClient插入Clob大文本问题
在Oracle中插入Clob数据时,当插入的数据<4K时,可以正常插入,但是>4K时,总是抱错:ORA-01704 文本太长。为了解决更大文本的插入,本人研究一下方法供参考,经测试没有问题。
在Oracle中插入Clob数据时,当插入的数据<4K时,可以正常插入,但是>4K时,总是抱错:ORA-01704 文本太长。为了解决更大文本的插入,本人研究一下方法供参考,经测试没有问题。
1
private 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![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
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![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
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![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
34
// 定义一个临时变量
35
OracleLob tempLob = (OracleLob)lobCmd.Parameters["tempLob"].Value;
36
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
37![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
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![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
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![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
58
// 提交事务
59
tx.Commit();
60![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
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
}
![](http://www.cnweblog.com/Images/OutliningIndicators/None.gif)
2
![](http://www.cnweblog.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
4
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
5
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
6
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
7
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
8
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
9
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
10
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
11
![](http://www.cnweblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
12
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
13
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
14
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
15
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
16
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
17
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
18
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
19
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
20
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
21
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
22
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
23
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
24
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
25
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
26
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
27
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
28
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
29
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
30
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
31
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
32
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
33
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
34
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
35
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
36
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
37
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
38
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
39
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
40
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
41
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
42
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
43
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
44
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
45
![](http://www.cnweblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
46
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
47
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
48
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
49
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
50
![](http://www.cnweblog.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
51
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
52
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
53
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
54
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
55
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
56
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
57
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
58
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
59
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
60
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
61
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
62
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
63
![](http://www.cnweblog.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
64
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
65
![](http://www.cnweblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
66
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
67
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
68
![](http://www.cnweblog.com/Images/OutliningIndicators/InBlock.gif)
69
![](http://www.cnweblog.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
70
![](http://www.cnweblog.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
posted on 2005-06-10 08:53 [Fenghua] 阅读(2234) 评论(2) 编辑 收藏 举报