ORA-01461 Error

[H Branyan]

ORA-01461 can bind a LONG value only for insert into a LONG column

Problem was originally discovered by simply putting in large amounts of text
into a textbox that would be inserted into a varchar2 field in the database.
The varchar2 type does not allow values greater than 4000 bytes to be
inserted. So, upon exceeding 4000 characters, this error was thrown.
However, entering 4000 characters, saving, then backspacing over 5
characters would also cause the error, despite that only 3995 character were
going into the update statement.

A variety of different numbers of characters were tried, with the same
problem happening even with just 2000 (1995 after backspacing) characters in
the box upon submits. Many things were attempted to see what might correct
the problem: cutting the box off at 4000 character did not correct it
because less characters could still potentially cause it. The encoding
schemes between the database and web browser were checked to ensure they
were the same single byte scheme. Matching these up did not correct the
problem. Trying to parse through the string before it was put in the update
statement and remove any non alphanumeric characters did not work.

Through all this we are led to believe that there is some type of issue in
ODP.NET, which handles the connectivity between the web and DB. Possibly
that it is somehow duplicating the data on the update, which causes anything
greater than 2000 characters to exceed the limit.

Has anyone come across this problem? How did you resolve it? (Occurs with
both .NET v1.0 and 1.1, and ODP.NET v9.2.0.2 and 9.2.0.4)

 

[Frans Bouma [C# MVP]]

ORA-01461 can bind a LONG value only for insert into a LONG column

Problem was originally discovered by simply putting in large amounts of text
into a textbox that would be inserted into a varchar2 field in the database.
The varchar2 type does not allow values greater than 4000 bytes to be
inserted. So, upon exceeding 4000 characters, this error was thrown.
However, entering 4000 characters, saving, then backspacing over 5
characters would also cause the error, despite that only 3995 character were
going into the update statement.

A variety of different numbers of characters were tried, with the same
problem happening even with just 2000 (1995 after backspacing) characters in
the box upon submits. Many things were attempted to see what might correct
the problem: cutting the box off at 4000 character did not correct it
because less characters could still potentially cause it. The encoding
schemes between the database and web browser were checked to ensure they
were the same single byte scheme. Matching these up did not correct the
problem. Trying to parse through the string before it was put in the update
statement and remove any non alphanumeric characters did not work.

Through all this we are led to believe that there is some type of issue in
ODP.NET, which handles the connectivity between the web and DB. Possibly
that it is somehow duplicating the data on the update, which causes anything
greater than 2000 characters to exceed the limit.

Has anyone come across this problem? How did you resolve it? (Occurs with
both .NET v1.0 and 1.1, and ODP.NET v9.2.0.2 and 9.2.0.4)

This error sounds very familiar, also the 2000 char limit which bugs it.
Some google searches revealed it had to do with the NLS_LANG setting for the
Oracle client and server which apparently are different. Making them matching
should solve the problem, however a customer of mine has similar problems
with ODP.NET (same error, also varchar2(2000), sending 2000 chars fails, 1000
succeeds) and NLS_LANG was correct...

Frans.

 

[H Branyan]

This error sounds very familiar, also the 2000 char limit which bugs it.
Some google searches revealed it had to do with the NLS_LANG setting for the
Oracle client and server which apparently are different. Making them matching
should solve the problem, however a customer of mine has similar problems
with ODP.NET (same error, also varchar2(2000), sending 2000 chars fails, 1000
succeeds) and NLS_LANG was correct...

Right, we have checked the NLS_LANG and made sure the web server used the
same single byte scheme. The actual problem seems to be very elusive, so
the solution is even less clear at this point.

Please post back if you find something that sheds some light on the true
cause of this error. I will do the same. Thanks.

 

[Frans Bouma [C# MVP]]

Right, we have checked the NLS_LANG and made sure the web server used the
same single byte scheme. The actual problem seems to be very elusive, so
the solution is even less clear at this point.

Please post back if you find something that sheds some light on the true
cause of this error. I will do the same. Thanks.

The customer who has problems can't save a 20 char string in a
varchar2(2000) field, he then gets the same error as you do. I can't
reproduce it here (with the exact same tables etc.) so it has to be something
else. When the length is set to something below 2000 (thus varchar2(1000) for
example), it works, the error is then not popping up.

As I can't reproduce it with oracle 9i, I can't see if I get the error also
with 10g.

So I'm out of options on this one, the error is very rare, but as with all
rare errors: a solution is hard to find

FB

 

[Frans Bouma [C# MVP]]

Right, we have checked the NLS_LANG and made sure the web server used the
same single byte scheme. The actual problem seems to be very elusive, so
the solution is even less clear at this point.

Please post back if you find something that sheds some light on the true
cause of this error. I will do the same. Thanks.

I solved it!

Here are the research results:
- it only happens with Varchar2(2000) fields in UTF-8 encoded databases.
- it only happens when you set the Parameter length to the length of the
field defined in the db.

So, say you have a Varchar2 field with length 4000 (which defaults to bytes),
in a schema located in a UTF-8 encoded database. If you then create an
ADO.NET query with a parameter for that field, and set the length to 4000,
you'll get the ORA-01461 error in that situation.

To fix this, simply do not set the size of the parameter!. The provider
will set the length then based on the amount of data specified in the value.
-> no more errors.

It's clear that there is somewhere a mixup/messup inside ODP.NET or the
oracle client: 2000 bytes varchar2 UTF-8 encoded fields can contain way less
than 2000 characters, but which end goes wrong here, I don't know. Anyway, by
omiting the size of the parameter, you can work around this without problems


Frans.