string length for VARCHAR2(4000)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
romanFleischer
Posts: 18
Joined: Wed 02 Jun 2010 16:28

string length for VARCHAR2(4000)

Post by romanFleischer » Wed 23 May 2012 10:49

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: string length for VARCHAR2(4000)

Post by Pinturiccio » Fri 25 May 2012 11:20

romanFleischer wrote: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
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Post by romanFleischer » Wed 30 May 2012 11:38

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
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Post by romanFleischer » Wed 30 May 2012 11:48

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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Post by Shalex » Thu 31 May 2012 11:33

romanFleischer wrote: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
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Post by romanFleischer » Thu 31 May 2012 12:10

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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Post by Shalex » Fri 01 Jun 2012 16:00

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
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Post by romanFleischer » Mon 04 Jun 2012 08:33

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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Post by Shalex » Tue 05 Jun 2012 15:49

romanFleischer wrote: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
Posts: 18
Joined: Wed 02 Jun 2010 16:28

Re: string length for VARCHAR2(4000)

Post by romanFleischer » Tue 05 Jun 2012 16:02

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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Post by Shalex » Fri 08 Jun 2012 16:52

romanFleischer wrote: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
Posts: 32
Joined: Fri 21 May 2010 15:47

Re: string length for VARCHAR2(4000)

Post by skoub » Wed 13 Jun 2012 18:30

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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Post by Shalex » Mon 18 Jun 2012 11:59

skoub wrote: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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Post by Shalex » Fri 22 Jun 2012 15:57

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
Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Re: string length for VARCHAR2(4000)

Post by Shalex » Mon 02 Jul 2012 07:03

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.

Post Reply