Home Database Mysql Tutorial Oracle中Clob类型处理解析

Oracle中Clob类型处理解析

Jun 07, 2016 pm 05:45 PM
connection data quot

系统环境 xp+2.0+oracle9i

  表结构(由于是测试,表结构随便建了一张) XX

  字段名

  类型

  ID

  VARCHAR2(70)

  TEST

  CLOB

  测试

  方式1:直接将CLOB的值拼写在SQL语句中。

  代码:

  string id = Guid.NewGuid().ToString();

  OracleCommand cmd = Conn.CreateCommand();

  cmd.CommandText = "insert into xx(id,test) values('" + id + "','" + data + "')";// data是一个变量,存储你要插入的字符串

  cmd.ExecuteNonQuery();

  情况分析:

  当data的长度大于4000时报错(ORA-01704:文字字符串过长),小于或等于4000时正常插入。

  原因分析:

  之所以会出现长度大于4000时报错,是因为Oracle中有SQL语句中两个单引号之间的字符数不能大于4000的限制。'" + data + "' data在sql语句之间,当data的值大于4000个字节时就会报错。

  解决办法:

  这种方式比较棘手,但有更好的方式,下边会讲到 。

  方式2:采用参数形式。

  代码:

  string id = Guid.NewGuid().ToString();

  OracleCommand cmd = Conn.CreateCommand();

  cmd.CommandText = "insert into xx(id,test) values('" + id + "',:p1)";

  OracleParameter p1 = new OracleParameter("p1", OracleType.Clob);

  p1.Value = data; // data是一个变量,存储你要插入的字符串

  cmd.Parameters.Add(p1);

  cmd.ExecuteNonQuery();

  情况分析:

  采用这种方式能够正常插入。所以推荐用这种方式。

  原因分析:

  无

  解决办法:

  无

  方式3:采用参数形式,但是参数类型写为OracleType. NVarChar

  代码:

  string id = Guid.NewGuid().ToString();

  OracleCommand cmd = Conn.CreateCommand();

  cmd.CommandText = "insert into xx(id,test) values('" + id + "',:p1)";

  OracleParameter p1 = new OracleParameter("p1", OracleType. NVarChar);

  p1.Value = data; // data是一个变量,存储你要插入的字符串

  cmd.Parameters.Add(p1);

  cmd.ExecuteNonQuery();

  

   情况分析:

  为什么要写这种方式,因为这种方式和采用NHibernate的方式很相似,先看看在这种方式会产生什么情况。当data的字节数在0-2000之间时正常插入,大于4000时也正常插入,但在2000-4000时则失败,报错(ORA-01461:仅可以插入 LONG列的LONG值赋值)

  原因分析:

  没有采用对应的Oracle类型。

  解决办法:

  采用OracleType.Clob

  下边采用NHibernate插入数据,NHibernate具体怎用不在本次讨论范围。

  NHibernate采用的版本为1.2.1.4000。

  下边大至把简要配置写下。

  App.config

  

  

  

  

  

  

  

  

  

  

  

  value="Data Source=Orcl_192.168.0.232;User ID =icqs_test;Password=icqs_test" />

  

  

  key="hibernate.adonet.batch_size"

  value="100"

  />

  

  

  xx.cs

  using System;

  using System.Collections.Generic;

  using System.Text;

  namespace Test.Enties

  {

  [Serializable]

  public class Xx

  {

  public Xx()

  {

  }

  private string id;

  public virtual string Id

  {

  get { return id; }

  set { id = value; }

  }

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to solve connection error How to solve connection error Nov 07, 2023 am 10:44 AM

How to solve connection error

What does CONNECTION_REFUSED mean? What does CONNECTION_REFUSED mean? Jul 31, 2023 pm 02:48 PM

What does CONNECTION_REFUSED mean?

PHP Warning: mysqli_connect(): (HY000/2002): Solution to Connection refused PHP Warning: mysqli_connect(): (HY000/2002): Solution to Connection refused Jun 23, 2023 am 08:54 AM

PHP Warning: mysqli_connect(): (HY000/2002): Solution to Connection refused

php提交表单通过后,弹出的对话框怎样在当前页弹出,该如何解决 php提交表单通过后,弹出的对话框怎样在当前页弹出,该如何解决 Jun 13, 2016 am 10:23 AM

php提交表单通过后,弹出的对话框怎样在当前页弹出,该如何解决

What data is in the data folder? What data is in the data folder? May 05, 2023 pm 04:30 PM

What data is in the data folder?

What to do if mysql load data is garbled? What to do if mysql load data is garbled? Feb 16, 2023 am 10:37 AM

What to do if mysql load data is garbled?

AI project failure rates top 80% — study cites poor problem recognition and a focus on latest tech trends among major problems AI project failure rates top 80% — study cites poor problem recognition and a focus on latest tech trends among major problems Aug 31, 2024 am 12:59 AM

AI project failure rates top 80% — study cites poor problem recognition and a focus on latest tech trends among major problems

What are the differences between xdata and data What are the differences between xdata and data Dec 11, 2023 am 11:30 AM

What are the differences between xdata and data

See all articles