CLOB type processing resolution in Oracle: ORA-01461: Only long values can be inserted into long column assignments

The analysis of CLOB type processing in the original Broken Sword ice-oracle

Previous analysis: ORA-01461: Can only bind a long value to insert a long column

Most recently, for the Clob field, when inserting data, the number of bytes in the character (one half-width character, one byte, one full-width character, two bytes) between 2000-4000 times is wrong (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 (70)
TEST	Clob

Test

Way 1 : Direct the CLOB the value is spelled in the SQL statement.

Code:

string id =="insert INTO XX (id,test) VALUES ('""" , '"')"; // data is a variable that stores the string cmd you want to insert . ExecuteNonQuery ();

Situation Analysis :

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

Cause Analysis:

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

Workaround:

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

Way 2 : Takes the form of a parameter.

Code:

  Guid.NewGuid (). ToString (); OracleCommand cmd  = Conn.createcommand (); cmd. CommandText  =  insert into XX (id,test) VALUES ('   '  + ID +  '    " ;oracleparameter p1  = Span style= "color: #0000ff;" >new  OracleParameter ( p1   "  = data; //  data is a variable that stores the string you want to insert  cmd. Parameters.Add (p1); cmd. ExecuteNonQuery ();  

Situation Analysis :

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

Cause Analysis:

No

Workaround:

No

Way 3 : Takes a Parameter form, but the parameter type is written as OracleType. NVarChar

Code:

  Guid.NewGuid (). ToString (); OracleCommand cmd  = Conn.createcommand (); cmd. CommandText  =  insert into XX (id,test) VALUES ('   '  + ID +  '    " ;oracleparameter p1  = Span style= "color: #0000ff;" >new  OracleParameter ( p1   "  = 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 this way is very similar to the way to adopt NHibernate, first see what happens in this way. When the number of bytes of data between 0-2000 normal insertion, more than 4000 when the normal insertion, but at 2000-4000 failed, error (ORA-01461: can only be inserted long column Long value assignment)

Cause Analysis:

The corresponding Oracle type is not used.

Workaround:

With Oracletype.clob