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 AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

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)

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

CONNECTION_REFUSED is a network connection error that usually occurs when trying to connect to a remote server. When a client device attempts to establish a network connection with a server, and the server rejects the connection request, a CONNECTION_REFUSED error is returned. Common reasons include: the server is not started, the server cannot accept more connection requests, the server firewall blocks the connection, etc.

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

Solution: 1. Check the network connection; 2. Check the server status; 3. Clear the cache and cookies; 4. Check the firewall and security software settings; 5. Try to use other networks, etc.

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

If you encounter the following error message when using PHP to connect to a MySQL database: PHPWarning:mysqli_connect():(HY000/2002):Connectionrefused, then you can try to solve this problem by following the steps below. To confirm whether the MySQL service is running normally, you should first check whether the MySQL service is running normally. If the service is not running or fails to start, it may cause a connection refused error. you can

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

php提交表单通过后,弹出的对话框怎样在当前页弹出php提交表单通过后,弹出的对话框怎样在当前页弹出而不是在空白页弹出?想实现这样的效果:而不是空白页弹出:------解决方案--------------------如果你的验证用PHP在后端,那么就用Ajax;仅供参考:HTML code

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

The data folder contains system and program data, such as software settings and installation packages. Each folder in the Data folder represents a different type of data storage folder, regardless of whether the Data file refers to the file name Data or the extension. Named data, they are all data files customized by the system or program. Data is a backup file for data storage. Generally, it can be opened with meidaplayer, notepad or word.

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

The differences are: 1. xdata usually refers to independent variables, while data refers to the entire data set; 2. xdata is mainly used to establish data analysis models, while data is used for data analysis and statistics; 3. xdata is usually used for regression Analysis, variance analysis, predictive modeling, data can be analyzed using various statistical methods; 4. xdata usually requires data preprocessing, and data can contain complete original data.

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

The solution to the garbled mysql load data: 1. Find the SQL statement with garbled characters; 2. Modify the statement to "LOAD DATA LOCAL INFILE "employee.txt" INTO TABLE EMPLOYEE character set utf8;".

More returns than sales: The Humane Ai Pin is becoming a commercial disaster More returns than sales: The Humane Ai Pin is becoming a commercial disaster Aug 08, 2024 pm 01:14 PM

Shortly after the launch of the Humane Ai Pin, scathing reviews revealed that the AI gadget was anything but ready for the market, as most of the originally advertised features either didn't work properly or were simply missing, the battery life was

See all articles