string length for VARCHAR2(4000)

[romanFleischer]

Hello,

now after we installed the verson 6.80.335 of dotConnect, we can not insert strings with correct length in our database.

When we try to insert an string with 2888 chars into a column defined as VARCHAR2(4000) - we get an error. LONG value..

It is only possible to insert strings of length smaler than 2001 characters. We changed realy nothing in our environment. We updated only the dotConnect version from 5.70 to 6.80. With the old version of dotConnect we do not have any problem. We use the direct mode to connect.

Code: Select all

<add name="ApplDataModel" connectionString="User Id=xxxx;Password=yyyy;Server=test.anywhere.com;Port=1521;Direct=True;Sid=Appl;Persist Security Info=True" providerName="Devart.Data.Oracle" />

<add name="dotConnect for Oracle" invariant="Devart.Data.Oracle" description="Devart dotConnect for Oracle" type="Devart.Data.Oracle.OracleProviderFactory, Devart.Data.Oracle, Version=6.80.325.0, Culture=neutral, PublicKeyToken=xxxxxxxxxx"/>

What do we have to change to make this version of dotConnect working?

Thanks Roman

[Pinturiccio]

now after we installed the verson 6.80.335 of dotConnect

There is no public version 6.80.335 of dotConnect for Oracle. There are versions 6.80.325 and 6.80.332. Please specify with which one the issue occurs.

We could not reproduce the issue. We can insert a string with any value greater than 2000 and less than 4000 in a column with type equal to varchar2(4000 BYTE) or varchar(4000 CHAR).
Could you please send us a small test project with DDL/DML scripts for reproducing the issue.

[romanFleischer]

Sorry i typed a mismatch - installed is of course 6.80.325.0 as displayed in our configuration file.

Of course we can give you a small example how we test this stange effect. We use .NET Entity Framework 4.0 and Oracle 11g.

When inserting more than 2000 characters into a VARCHAR2 (4000 CHAR) db column, we get an Oracle Error.

Devart.Data.Oracle.OracleException: ORA-01461: can bind a LONG value only for insert into a LONG column

If we try to insert 2000 characters into a VARCHAR2 (2000 CHAR), it works! (We use the same table and the same enitity for our test)

Here is the definition of our table:

Code: Select all

CREATE TABLE MODUL (
  MODUL_ID NUMBER(5),
  ZERTIFIKATSTEXT VARCHAR2(4000 CHAR),
  TEXT VARCHAR2(2000 CHAR)
)

the entity class:

Code: Select all

public partial class Module
{
	//Id-Fields
	public int Id { get; set;}
	// Attributes
	public String CertificateText { get; set;}
	public String Text { get; set;}
}

the entity mapping:

Code: Select all

private void MapModule(ModelBuilder modelBuilder)
{
	// table-assignment
	modelBuilder.Entity<Domain.Module>().MapSingleType(entity => new
	{
		MODUL_ID = entity.Id
		,TEXT = entity.Text
		,ZERTIFIKATSTEXT = entity.CertificateText
	}).ToTable("MODUL");
	// Id
	modelBuilder.Entity<Domain.Module>().Property(entity => entity.Id).StoreGeneratedPattern = StoreGeneratedPattern.Identity;
	modelBuilder.Entity<Domain.Module>().HasKey(entity => entity.Id);
	// Properties
    modelBuilder.Entity<ebis.Domain.Sibis.Module>().Property(entity => entity.CertificateText).IsNotUnicode();
}

And then we only call the SaveChanges() method of the Entity.Context

[romanFleischer]

We found a workaround for our problem. If we insert folowing line into our entity mapping:

Code: Select all

...
modelBuilder.Entity<Domain.Module>().Property(entity => entity.CertificateText).IsNotUnicode();

we can insert 4000 charcters into the VARCHAR2(4000 CHAR) column.

But i hope this is not the only solution, because we have more than 2000 string columns in our solution and it will take some time to insert this line for every column. Also the mapping is generated automaticaly from an UML model and can not be modified every time.

This is the complete exception code of our solution and devart together. Mabe this will help analyze the problem.

Code: Select all

---> Devart.Data.Oracle.OracleException: ORA-01461: can bind a LONG value only for insert into a LONG column     bei Devart.Data.Oracle.t.d()     bei Devart.Data.Oracle.aq.h()     bei Devart.Data.Oracle.aq.c()     bei Devart.Data.Oracle.aa.a(Int32 A_0, bt A_1)     bei Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)     bei Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)     bei Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()     bei Devart.Data.Oracle.Entity.e.j()     bei System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)     bei System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)     --- Ende der internen Ausnahmestapelüberwachung ---     bei System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)     bei System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)     bei System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)     bei System.Data.Entity.DbContext.SaveChanges()     bei Sirius.EntityFramework.DbContextManager.SiriusPocoBaseDao`2.SaveAllObjectChanges()     bei ebis.Domain.Sibis.ModuleDao.Change(Module entity) in d:\Projekte-Zusatz\ebis2\Deployment\tfsCheckout\ebis.Domain.Sibis\target\ModuleDao.g.cs:Zeile 104.     bei ebis.PlanningAdministration.Core.TrainingCourseService.HandleSaveModule(ModuleVO 
...

[Shalex]

When we try to insert an string with 2888 chars into a column defined as VARCHAR2(4000) - we get an error. LONG value..
It is only possible to insert strings of length smaler than 2001 characters.

It seems like the character set for VARCHAR2 at your server is multibyte (two bytes per character in your case). Character set is determined by server's NLS_CHARACTERSET setting, the current value can be found out with the following query:

Code: Select all

select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'

[romanFleischer]

I get the result AL32UTF8
I wonder why it ist possible to insert 1999 character into a VARCHAR2(2000 CHAR) column but not possible to insert 2888 character into a VARCHAR2(4000 CHAR) colum?

Do the columns use different character sets dependent on the specified length, or does oracle handle VARCHARS in different ways?

[Shalex]

Please refer to https://forums.oracle.com/forums/thread ... D=10272011: "The maximum size limit for a VARCHAR2 column is 4000 bytes regardless of CHAR semantics".

[romanFleischer]

Sounds good, but the reason for our problem can not be the Oracle database.
Why not? If we enter the following code into our entity mapping - everything works well without any change on the database.

Code: Select all

modelBuilder.Entity<Domain.Module>().Property(entity => entity.CertificateText).IsNotUnicode();

It is also posssible to enter 4000 characters by using a simple insert into, oder update SQL-Statement.

Newly we have also to check the "use unicode" checkbox inside our VisualStudio Database Explorer. This was also not necessary with our former used version 5.70. The databse has not been changed for the last 4 month.

[Shalex]

Why not? If we enter the following code into our entity mapping - everything works well without any change on the database.

Code: Select all

modelBuilder.Entity<Domain.Module>().Property(entity => entity.CertificateText).IsNotUnicode();

The reason is that Entity Framework considers the string property as a unicode column by default now.

[romanFleischer]

This is a helpfull information, but we still use the .NET 4.0 Framework (and the same version of Entity Framework). Nothing has changed since our update of the dotConnect. Is the new dotConnect version no longer compatible with older Entity Framework?

[Shalex]

Nothing has changed since our update of the dotConnect. Is the new dotConnect version no longer compatible with older Entity Framework?

There was the bug in the old versions of dotConnect for Oracle when provider treated unicode columns as non-unicode.
We are investigating the possibility to implement a workaround for changing string mappings in the Code-First models which are not configured completely (your case).

[skoub]

I also have this problem. Everything was fine with v6.50.237 and today we have upgraded to v7.0.17 and now i get the error "ORA-12704: character set mismatch".

We are working with CodeFirst! What have changed since the v6.50.237??

[Shalex]

What have changed since the v6.50.237??

As we answered in the previous post, we are investigating the possibility to implement a workaround for changing string mappings in the Code-First models which are not configured completely to treat unicode columns as non-unicode (the incorrect behaviour of the previous versions). We will post here about the results.

[Shalex]

romanFleischer and skoub, we have sent the internal build of dotConnect for Oracle to you with the implemented config.CodeFirstOptions.UseNonUnicodeStrings option. Please test it in your environment and notify us about the results.

[Shalex]

The config.CodeFirstOptions.UseNonUnicodeStrings configuration option (default value is False) is added in order to treat string properties without an explicitly specified server data type, as non-unicode.

We will post here when the corresponding public build of dotConnect for Oracle is available for download.