CLOB type processing parsing in Oracle

System Environment xp+.net2.0+oracle9i

Table structure (because it is a test, the table structure casually built a sheet) XX

Field name

Type

Id

VARCHAR2 (70)

TEST

Clob

Test

Mode 1: Spell the CLOB value directly in the SQL statement.

Code:

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

OracleCommand cmd = Conn.createcommand ();

Cmd.commandtext = "INSERT INTO XX (id,test) VALUES (' + ID +" ', ' "+ Data + ')";/data is a variable that stores the string you want to insert

Cmd. ExecuteNonQuery ();

Situation Analysis:

When the length of data is greater than 4000 times error (ORA-01704: literal string is too long), is less than or equal to 4000 when normal insertion.

Reason Analysis:

The reason for the length greater than 4000 times error is that there is no limit of the number of characters between two single quotes in the SQL statement in Oracle that is greater than 4000. ' + data + ' data between SQL statements, when the value of data is greater than 4,000 bytes will be an error.

Solution:

This is a tricky way, but there's a better way to do it.

Mode 2: Adopt the Parameter form.

Code:

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

OracleCommand cmd = Conn.createcommand ();

Cmd.commandtext = "INSERT INTO XX (id,test) VALUES (' + ID +" ',:p 1) ";

OracleParameter P1 = new OracleParameter ("P1", Oracletype.clob);

P1. Value = data; Data is a variable that stores the string you want to insert

Cmd. Parameters.Add (p1);

Cmd. ExecuteNonQuery ();

Situation Analysis:

This is the way to insert normally. So it's recommended in this way.

Reason Analysis:

No

Solution:

No

Mode 3: The Parameter form is used, but the parameter type is written as OracleType. NVarChar

Code:

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

OracleCommand cmd = Conn.createcommand ();

Cmd.commandtext = "INSERT INTO XX (id,test) VALUES (' + ID +" ',:p 1) ";

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

P1. Value = data; Data is a variable that stores the string you want to insert

Cmd. Parameters.Add (p1);

Cmd. ExecuteNonQuery ();

　　

Situation Analysis:

Why write this way, because it's very similar to the way you use NHibernate, and see what happens in this way first. When the number of bytes in data is inserted normally between 0-2000, and is inserted normally when greater than 4000, but fails at 2000-4000, error (ORA-01461: can only insert a long value assigned to a long column)

Reason Analysis:

The corresponding Oracle type is not used.

Solution:

Using Oracletype.clob

The bottom uses nhibernate inserts the data, the nhibernate concrete how uses does not have this discussion scope.

The NHibernate version is 1.2.1.4000.

The bottom is big to write down the brief configuration.

App.config

<?xml version= "1.0" encoding= "Utf-8"?>

<configuration>

<configSections>

<section name= "NHibernate" type= "System.Configuration.NamueSectionHandler, System, version=1.0.5000.0,culture= Neutral, publickeytoken=b77a5c561934e089 "/>

</configSections>

<nhibernate>

<add key= "Hibernate.connection.provider" value= "NHibernate.Connection.DriverConnectionProvider"/>

<add key= "Hibernate.connection.driver_class" value= "NHibernate.Driver.OracleClientDriver"/>

<add key= "hibernate.connection.isolation" value= "readcommitted"/>

<add key= "Hibernate.dialect" value= "NHibernate.Dialect.Oracle9Dialect"/>

<add key= "Hibernate.connection.connection_string"

Value= "Data source=orcl_192.168.0.232; User ID =icqs_test; Password=icqs_test "/>

<add key= "Show_sql" value= "true"/>

<add

key= "Hibernate.adonet.batch_size"

Value= "100"

/>

</nhibernate>

</configuration>

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;}

}