Parsing of CLOB type processing in Oracle

Summary

The most recent use of the NHibernate mapping type for the Clob field when inserting data is when the number of bytes (a half-width character, one byte, one full-width character, two bytes) between 2000-4000 times is incorrect (ORA-01461: You can only insert a Long value assignment for a long column). After constantly looking for information and their own test the problem finally solved, below I will own experience to do a share.

Get ready

System Environment xp+.net2.0+oracle9i

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

Field name	type
ID	VARCHAR2 (+)
TEST	CLOB

TestMethod 1: Spell the value of the CLOB 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 cmd you want to insert. ExecuteNonQuery ();

Situation Analysis :

when the length of D ATA is greater than the 4000 times wrong (ORA-01704: The literal string is too long )and is inserted normally whenit is less than or equal to 4000 .

cause Analysis:

   The reason why the length is greater than4000The times are wrong becauseOraclein aSQLthe number of characters between two single quotes in a statement cannot be greater than4000the restrictions. ' "+ data + "' Databetween SQL statements, when the value of data is greater than4000byteswill be an error.

Workaround:

This approach is tricky, but there are better ways to do it below.

Mode 2: Use 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);p 1. Value = data; Data is a variable that stores the string cmd you want to insert. Parameters.Add (p1); cmd. ExecuteNonQuery ();

Situation Analysis :

this way you can insert normally. So recommended in this way.

cause Analysis:

No

Workaround:

no

Mode 3: Takes the Parameter form, 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);p 1. Value = data; Data is a variable that stores the string cmd you want to insert. Parameters.Add (p1); cmd. ExecuteNonQuery ();

Situation Analysis :

       Why write this way, because this way and adopt NHibernate data 0-2000 4000 -4000 + Span style= "font-family: the song Body;" The error ( ORA-01461: can only insert LONG LONG )

cause Analysis:

The corresponding Oracle type is not used .

Workaround:

Use OracleType . Clob

Parsing of CLOB type processing in Oracle