In my Oracle project I have a table that contains 3 columns with the type of VARCHAR2(2000). When I update my entity mapped to this table through LLBLGen, I get the following exception:

Topaz.Test.BL.ServiceClasses.AuditTrailServiceTest_Oracle.AuditTest : SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of an action query: ORA-01461: can bind a LONG value only for insert into a LONG column. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

If I change the data type of two of the columns to VARCHAR2(200), update works. Does it mean that I can have only one VARCHAR2(2000) column in the table?

Please help.

I'm encountering this same error when updating a table with two varchar(2000) columns. If I shorten one field to 1001 characters in PersistenceInfoFactory, the error doesn't occur.

Any ideas Frans?

Thanks Ryan Ellis

This is reported before but I can't reproduce it

The customer reporting it sent me a table layout I tried here on 9i and I ran it without problems. I couldn't find anything that might be the cause of this. The only thing I could find on google groups is that other people (f.e. using Java and Oracle) run into this error as well and often the language setting differs from client and server. (NLS_LANG env setting or something)

In the ADO.NET newsgroup a person reported this issue with normal ADO.NET code and ODP.NET a couple of weeks ago and also didn't find any solution.

The weird thing is that apparently the code released on June 28th (thus with the new designer) fails, but code released before that doesn't. (runtime libs).

What has changed in the runtime library released with the v1.0.2004.1 designer is that INSERT queries (the error seemd to happen with INSERTs with that other customer) with a sequenced column are now split up in 2 separate queries (first the sequence value is retrieved with a scalar query, then that value is passed to the INSERT query) and not a batched query as it was before. This is done because 10g doesn't support batched queries with ADO.NET, and the code is shared with 10g.

If someone could give me a table layout which always fails and details if that table is sequenced or not, the version of ODP.NET (I use an older version of ODP.NET 9.2), I can try again to reproduce it here.

(edit): see this google groups thread

At the moment I have a hard time to get even 1 page to show on the otn.oracle.com forums so I can't search there for this error there or ask around...

What I'd like you to ask is if a normal ADO.NET query (so perhaps first grab the sql generated from the adapter, then create an OracleCommand object and do the query manually) also throws this error. Also, is the text inserted exceeding 2000 chars or not? (the other customer reporting this just tried to insert 20 chars and it gave the exception).

Also, if the varchar columns are defined with 3999 chars as the length, does it work then?

The table syntax is the following:

CREATE TABLE AUDIT_TRAIL ( 
  AUDIT_ID                NUMBER (8)    NOT NULL, 
  CHANGE_DATETIME          DATE, 
  ORIGINAL_CREATE_DATETIME  DATE, 
  CHANGE_USERNAME          VARCHAR2 (120), 
  CHANGE_COMMENTS          VARCHAR2 (2000), 
  TABLE_NAME                VARCHAR2 (30), 
  COLUMN_NAME              VARCHAR2 (30), 
  PRIMARY_KEY_VALUES        VARCHAR2 (2000), 
  REFERENCE              VARCHAR2 (2000), 
  CONSTRAINT PK_AUDIT_TRAIL
  PRIMARY KEY ( AUDIT_ID ) ) ;

The sequencer exists for the AUDIT_ID column. The version of ODP.NET is 9.2.0.4.01. Inserts failed when I tried to insert values with 20-30 charachters long. I'll try to change the column size to 3999 and let you know.

OK thanks.

Also check the NLS_LANG setting on both client and server, and define that setting, if not already done so and make them matching.

AuditTrailEntity audit = new AuditTrailEntity();
audit.ChangeDatetime = DateTime.Now;
audit.OriginalCreateDatetime = DateTime.Now;
audit.ChangeUsername = "Frans";
audit.ChangeComments = "This is a comment that's quite long. This is a comment that's quite long. This is a comment that's quite long. This is a comment that's quite long. This is a comment that's quite long. ";
audit.TableName = "Foo";
audit.ColumnName = "Bar";
audit.PrimaryKeyValues = "This is a comment that's quite long. This is a comment that's quite long. This is a comment that's quite long. This is a comment that's quite long. This is a comment that's quite long. ";
audit.Reference = "This is a comment that's quite long. This is a comment that's quite long. This is a comment that's quite long. This is a comment that's quite long. This is a comment that's quite long. This is a comment that's quite long. ";

DataAccessAdapter adapter = new DataAccessAdapter();
bool saveResult = adapter.SaveEntity(audit);
Console.WriteLine("Save succesful: {0}", saveResult);

Which displays 'true', and indeed a row is inserted.

This is with the 9.2.0.2102 ODP.NET version, targeting Oracle 9i on windows 2000 enterprise server, using the latest llblgen pro runtime libraries. So I'm really puzzled here what it can be.

THe NLS_LANG setting on the server is 'AMERICAN', which is the default (as I haven't changed it), I don't know how to see what the client NLS_LANG setting is.

I've executed the same code as you did and got an exception: ORA-01461: can bind a LONG value only for insert into a LONG column.

I've checked my client NLS_LANG by executing the following statement: SELECT USERENV ('LANGUAGE') FROM DUAL My setting is: AMERICAN_AMERICA.AL32UTF8. I've made sure that registry has the same value. Don't know what else to do...

alex wrote:

I've executed the same code as you did and got an exception: ORA-01461: can bind a LONG value only for insert into a LONG column.

I've checked my client NLS_LANG by executing the following statement: SELECT USERENV ('LANGUAGE') FROM DUAL My setting is: AMERICAN_AMERICA.AL32UTF8. I've made sure that registry has the same value. Don't know what else to do...

Could you try to grab the SQL used by deriving from DataAccessAdapter and in that class override OnSaveEntity() which gets the Query executed? After that, could you create a small snippet of code using normal ADO.NET statements (OracleConnection and OracleCommand) and execute the same query ?

I'd like to know if it's my code or if it's ado.net or oracle's ODP.NET.

Also, you're using the same ODP.NET version I have or the later version?

(btw, isn't that returning the server's NLS_LANG value?)

In OnSaveEntity by looking at the saveQuery.Command.CommandText I can get a SQL statement that uses parameters. How to get parameter values? The SQL statement is the followind:

"INSERT INTO \"GRANITE\".\"AUDIT_TRAIL\" (\"AUDIT_ID\",\"CHANGE_DATETIME\",\"ORIGINAL_CREATE_DATETIME\", \"CHANGE_USERNAME\",\"CHANGE_COMMENTS\",\"TABLE_NAME\", \"COLUMN_NAME\",\"PRIMARY_KEY_VALUES\",\"REFERENCE\") VALUES (:AuditId,:ChangeDatetime,:OriginalCreateDatetime,:ChangeUsername, :ChangeComments,:TableName,:ColumnName,:PrimaryKeyValues,:Reference)"

The version of ODP.NET is 9.2.0.4.01, which I believe is later than yours. I'm not sure whether the SELECT USERENV ('LANGUAGE') FROM DUAL statement returns the client or server settings. On the Web site that I looked at it said that this statement returned the client settings. If this is the server settings, then how to get to the client's?

Also, if you've noticed, we are using UTF8 character set. Could this create a problem? I can try to re-create the database with ASCII character set and see if it makes a difference.

alex wrote:

In OnSaveEntity by looking at the saveQuery.Command.CommandText I can get a SQL statement that uses parameters. How to get parameter values?

THey're in the actionQuery.Command.Parameters collection.

The version of ODP.NET is 9.2.0.4.01, which I believe is later than yours.

Ok. I'll try to install that one to retest.

I'm not sure whether the SELECT USERENV ('LANGUAGE') FROM DUAL statement returns the client or server settings. On the Web site that I looked at it said that this statement returned the client settings. If this is the server settings, then how to get to the client's?

I really don't know . However what I can think of is that a select executed on the server reports the language setting at the server.

Also, if you've noticed, we are using UTF8 character set. Could this create a problem? I can try to re-create the database with ASCII character set and see if it makes a difference.

UTF8 is also the charset used by the customer reporting this issue earlier. I test this by adding the table to the SCOTT schema. I'm not sure if that creates an ASCII character based table, it might.

I'll try to create a new schema and with UTF8.

Also, wild guess: could you lookup the varchar columns in the PersistenceInfoFactory.cs class and change OracleDbType.Varchar2 into OracleDbType.NVarchar2, compile the code again and re-run?

Changing to NVarchar2 didn't work.

alex wrote:

Changing to NVarchar2 didn't work.

Hmm.

Brainstorming here: Well, what could be the problem is that the column on the server is 2000 BYTES long, but in fact < 2000 CHARS long, as it is defined as UTF8, which stores characters in 1-4 bytes, normally in 1-3 bytes.

The length of the parameter defined for the varchar2 fields is set to 2000. As the type is Varchar2, it might be that ODP.NET thinks that this is is too long, as 2000 characters, encoded in UTF8 can never fit in 2000 bytes. After that, it is really checking hte data. Just guessing here...

Anyway, I'm going to create a UTF-8 encoded db now and see what the code then gives me..

my language setting seems to be: DUTCH_THE NETHERLANDS.WE8MSWIN1252 The server is set to AMERICA (NLS_LANG setting).

(btw, you were right about this function)

Otis wrote:

The length of the parameter defined for the varchar2 fields is set to 2000. As the type is Varchar2, it might be that ODP.NET thinks that this is is too long, as 2000 characters, encoded in UTF8 can never fit in 2000 bytes. After that, it is really checking hte data. Just guessing here...

What you said is true for varchar2 columns with any size. Why only columns of the certain size create a problem? By the way, I changed the size of my big columns from 2000 to 1999 and still had a problem. So, I wonder, this problem appears starting from what column size?

I really appreciate your help with this issue. I think it's an Oracle problem, not LLBLGen's.

alex wrote:

Otis wrote:

The length of the parameter defined for the varchar2 fields is set to 2000. As the type is Varchar2, it might be that ODP.NET thinks that this is is too long, as 2000 characters, encoded in UTF8 can never fit in 2000 bytes. After that, it is really checking hte data. Just guessing here...

What you said is true for varchar2 columns with any size. Why only columns of the certain size create a problem?

Because the max size in BYTES is always 4000. So UTF-8 character strings can be maxlength 4000/3 (I just started reading globalisation support in the oracle manuals) = 1333.

I have a problem with understanding how Oracle handles multibyte chars. With SQLServer, you have, as with oracle, N<type> types, like NVarchar, NChar etc. Oracle has these two. Using NVarchar on sqlserver stores the string as a multibyte string, on Oracle it does too. However, if you use a varchar2 field on oracle, using the UTF8 encoding, does a string get stored in multi-byte fashion as well? If so, teh length set on the CLIENT is wrong (in the llblgen pro code) as it defines a length of 2000, but it should be 2000/3 is 667, when using UTF8, as the server defined 2000 bytes as length for the column.

So another question to you is: When you change the length parameter in the PersistenceInfoFactory.cs class of these varchars fields from 2000 to 667 does it work then? If so, how high can you crank it up before it fails? (so keep the columns at the server at length 2000.

I really appreciate your help with this issue. I think it's an Oracle problem, not LLBLGen's.

I'm not entirely sure it's all Oracle's fault. (well, providing better insight in waht's wrong is their fault of course). Perhaps I defined the length in the parameter wrong. It's a bit of a mess what the value in the length of the column definition defines: bytes or chars....

Ok, perhaps this is something:

(from the help of 'size' in the table definition screen in enterprise manager console) Size: You can specify the length in bytes or characters when you are connected to a unicode database and you want to set a column to a different type than what is defaulted by the database. For example, instead of entering "10" as the length of a column, you can enter "10 byte" or "10 char" to specify whether the "10" is in bytes or chars. If byte/char is not specified, then the database will use whatever NLS_LENGTH_SEMANTICS is set to. By default, it is set to byte.

It defaults to bytes. If you change the column definition's size from 2000 to 2000 char, would that work? (in enterprise manager console, thus on the server)

I can't get the CLIENT encoding set to UTF8, all registry entries are changed but I keep AMERICAN_AMERICA.WE8MSWIN1252. Which is 8bit ascii...

If someone can give me a hint, please feel free to do so

I ran the test on the not unicode database (actually it was Oracle 8 ) and got the same error. It looks like the problem is not related to Unicode settings. So, why you cannot reproduce it? I've also tried to change from 2000 to 2000 char and it didn't help. When I'm switching the size to 667 in the PersistenceInfoFactory, it works. Do you want me to go up and tell you at what size it fails?

It looks like the critical size is 1000. If I go above it, I get the error.

alex wrote:

It looks like the critical size is 1000. If I go above it, I get the error.

Hmmm.

This doesn't match with the 3 byte per char thingy...

I really don't understand why it's half the length defined. The non-UTF8 db, if you execute SELECT USERENV('LANGUAGE') FROM DUAL, what's the charsetting then? Your client is still UTF8 I think...

Why I can't reproduce it I don't know. my charsetting is perhaps 8 bit/char and works always, I don't know. I can't get the CLIENT to become UTF8. I have the feeling as soon as that's the case, I can reproduce it. If you know how to do that, I'd be very happy

Frans,

When I tried on my Oracle 8 database, I forgot to change NLS_LANG settings in my registry. After I syncronized my client and server NLS_LANG settings, it worked on Oracle 8. Also, I created a not unicode Oracle 9 database, and it worked too.

So, it looks like this problem appears in Oracle unicode databases. I don't think there is a way to change your client to UTF8 by flipping some switch. I believe, you need to create a new database selecting UTF8 character set at creation time.

Forgot to tell you about NLS_LANG settings in my databases: for unicode database: AMERICAN_AMERICA.AL32UTF8 for non-unicode database: AMERICAN_AMERICA.WE8MSWIN1252

alex wrote:

Frans,

When I tried on my Oracle 8 database, I forgot to change NLS_LANG settings in my registry. After I syncronized my client and server NLS_LANG settings, it worked on Oracle 8. Also, I created a not unicode Oracle 9 database, and it worked too.

So, it looks like this problem appears in Oracle unicode databases. I don't think there is a way to change your client to UTF8 by flipping some switch. I believe, you need to create a new database selecting UTF8 character set at creation time.

Aha! thanks for testing!

Based on this I'm pretty confident I have a workaround for you. I'll simply not set the Size property of the Parameters, so the driver has to fill that one in based on the value provided. I've mailed you a .zip file (to the email address you provided to the forum) with an updated DQE for oracle (debug build). Please let me know if this one fixes your problem.