LinqToOracle SubmitChanges failure with Varchar2 types

[njappboy]

I'm running into a strange behavior with simple insert and updates on VARCHAR2 fields using LinqToOracle (lastest build).

The environment:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
Devart.Data.Oracle version 6.10.126.0
Devart.Data.Oracle.Linq version 2.20.17.0
Devart.Data.Linq version 2.20.17.0
.NET 4 Framework

I've broken the issue down to a small sample table:

CREATE TABLE TEST
(
NAME2 VARCHAR2(256 CHAR) NOT NULL,
ID NUMBER NOT NULL --INDEX ADDED TO CREATE PK
)

-- ID column is populated with this sequence, add it functions properly
CREATE SEQUENCE SEQ_TEST
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;


LinqToOracle is producing different errors depending on the "Update Check" setting of the Name2 field in my "Test" entity.

If I set the Test.Name2 property to Update Check = "Always" the following code fails on the 3rd SubmitChanges() call:

Code: Select all

var dx = new DbContext.ObjectDataContext();


var ut = new ObjectContext.Test();
ut.Name2 = "Test";
dx.Tests.InsertOnSubmit( ut );

var ut2 = new ObjectContext.Test();
ut2.Name2 = "Test 2";
dx.Tests.InsertOnSubmit( ut2 );

dx.SubmitChanges();
var id = ut2.Id;
ut2 = null;
			
var ut3 = dx.Tests.First( x => x.Id == id );
ut3.Name2 = "Test 3";
Console.WriteLine( ut3.Name2 );
dx.SubmitChanges(); // ERRORS HERE

LinqToOracle Log:
INSERT INTO DB_TESTING.TEST (NAME2, ID) VALUES (:p1, :p2) RETURNING ID INTO :ret1
-- p1: Input VarChar (Size = 4; DbType = String) [Test]
-- p2: Input Number (Size = 0; DbType = Decimal) [33]
-- ret1: Output Number (Size = 0; DbType = Decimal) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: ao Build: 2.20.17.0

INSERT INTO DB_TESTING.TEST (NAME2, ID) VALUES (:p1, :p2) RETURNING ID INTO :ret1
-- p1: Input VarChar (Size = 6; DbType = String) [Test 2]
-- p2: Input Number (Size = 0; DbType = Decimal) [34]
-- ret1: Output Number (Size = 0; DbType = Decimal) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: ao Build: 2.20.17.0

SELECT t1.NAME2, t1.ID
FROM (
SELECT t2.NAME2, t2.ID, ROWNUM AS "rnum"
FROM DB_TESTING.TEST t2
WHERE t2.ID = :p0
) t1
WHERE t1."rnum" x.Id == id );
ut3.Name2 = "Test 3";
Console.WriteLine( ut3.Name2 );
dx.SubmitChanges(); // NO ERROR [/code]

INSERT INTO DB_TESTING.TEST (NAME2, ID) VALUES (:p1, :p2) RETURNING ID INTO :ret1
-- p1: Input VarChar (Size = 4; DbType = String) [Test]
-- p2: Input Number (Size = 0; DbType = Decimal) [39]
-- ret1: Output Number (Size = 0; DbType = Decimal) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: ao Build: 2.20.17.0

INSERT INTO DB_TESTING.TEST (NAME2, ID) VALUES (:p1, :p2) RETURNING ID INTO :ret1
-- p1: Input VarChar (Size = 6; DbType = String) [Test 2]
-- p2: Input Number (Size = 0; DbType = Decimal) [40]
-- ret1: Output Number (Size = 0; DbType = Decimal) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: ao Build: 2.20.17.0

SELECT t1.NAME2, t1.ID
FROM (
SELECT t2.NAME2, t2.ID, ROWNUM AS "rnum"
FROM DB_TESTING.TEST t2
WHERE t2.ID = :p0
) t1
WHERE t1."rnum" x.Id == id );
ut3.Name2 = "Test 3";
ut3.Name3 = "New Test 3";
Console.WriteLine( ut3.Name2 );
dx.SubmitChanges(); [/code]

INSERT INTO DB_TESTING.TEST (NAME2, NAME3, ID) VALUES (:p1, :p2, :p3) RETURNING ID INTO :ret1
-- p1: Input VarChar (Size = 4; DbType = String) [Test]
-- p2: Input VarChar (Size = 8; DbType = String) [New Test]
-- p3: Input Number (Size = 0; DbType = Decimal) [42]
-- ret1: Output Number (Size = 0; DbType = Decimal) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: ao Build: 2.20.17.0



Message: Error on executing DbCommand.

StackTrace: at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)
at Devart.Data.Linq.h.a(SubmitCommand A_0, SubmitCommandBuilder A_1)
at Devart.Data.Linq.r.a(SubmitedObject A_0, SubmitCommand A_1, SubmitCommandBuilder A_2)
at Devart.Data.Linq.s.a(l A_0, ad A_1, Object A_2, Boolean A_3)
at Devart.Data.Linq.y.a(s A_0, ad A_1, Boolean A_2)
at Devart.Data.Linq.y.a(DataContext A_0, ConflictMode A_1)
at Devart.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
at Devart.Data.Linq.DataContext.SubmitChanges()
at LinqToOracleSample1.Program.Main(String[] args) in C:\Users\cwagner\documents\visual studio 2010\Projects\LinqToOracleSample1\LinqToOracleSample1\Program.cs:line 29
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

InnerException.Message: ORA-01461: can bind a LONG value only for insert into a LONG column

InnerException.StackTrace: at Devart.Data.Oracle.a0.b(Int32 A_0)
at Devart.Data.Oracle.a2.a(Int32 A_0, bj A_1)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Devart.Data.Linq.Provider.DataProvider.ExecuteQuery(String commandText, List`1 resultsetParameters, IList`1 parameters, Boolean isBatch, IDbCommand& dbCommand)




Can you please help me resolve this issue ASAP? My company is looking into purchasing the dotConnect for Oracle Professional Team license but without a resolution to this core functionality I'm afraid we will have to look for another solution.

[njappboy]

Please note: I've also tried the same scenarios with Name2 and Name3 as NVARCHAR2 with the same results.


Here is the code generated by the LinqToOracle lqml designer:

Code: Select all

namespace ObjectContext
{


    /// 
    /// There are no comments for ObjectContext.Test in the schema.
    /// 
    [Table(Name = @"DB_TESTING.TEST")]
    public partial class Test : INotifyPropertyChanging, INotifyPropertyChanged    {

        private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(System.String.Empty);

        private string _Name2;

        private decimal _Id;

        private string _Name3;
    
        #region Extensibility Method Definitions
        partial void OnLoaded();
        partial void OnValidate(System.Data.Linq.ChangeAction action);
        partial void OnCreated();
        partial void OnName2Changing(string value);
        partial void OnName2Changed();
        partial void OnIdChanging(decimal value);
        partial void OnIdChanged();
        partial void OnName3Changing(string value);
        partial void OnName3Changed();
        #endregion

        public Test()
        {
            OnCreated();
        }

    
        /// 
        /// There are no comments for Name2 in the schema.
        /// 
        [Column(Name = @"NAME2", Storage = "_Name2", CanBeNull = false, DbType = "VARCHAR2(256) NOT NULL")]
        public string Name2
        {
            get
            {
                return this._Name2;
            }
            set
            {
                if (this._Name2 != value)
                {
                    this.OnName2Changing(value);
                    this.SendPropertyChanging();
                    this._Name2 = value;
                    this.SendPropertyChanged("Name2");
                    this.OnName2Changed();
                }
            }
        }

    
        /// 
        /// There are no comments for Id in the schema.
        /// 
        [Column(Name = @"ID", Storage = "_Id", AutoSync = AutoSync.OnInsert, CanBeNull = false, DbType = "NUMBER NOT NULL", IsPrimaryKey = true)]
        [Devart.Data.Linq.Mapping.KeyGenerator.SequenceGenerator(Sequence = "SEQ_TEST")]
        public decimal Id
        {
            get
            {
                return this._Id;
            }
            set
            {
                if (this._Id != value)
                {
                    this.OnIdChanging(value);
                    this.SendPropertyChanging();
                    this._Id = value;
                    this.SendPropertyChanged("Id");
                    this.OnIdChanged();
                }
            }
        }

    
        /// 
        /// There are no comments for Name3 in the schema.
        /// 
        [Column(Name = @"NAME3", Storage = "_Name3", CanBeNull = false, DbType = "VARCHAR2(256) NOT NULL")]
        public string Name3
        {
            get
            {
                return this._Name3;
            }
            set
            {
                if (this._Name3 != value)
                {
                    this.OnName3Changing(value);
                    this.SendPropertyChanging();
                    this._Name3 = value;
                    this.SendPropertyChanged("Name3");
                    this.OnName3Changed();
                }
            }
        }
   
        public event PropertyChangingEventHandler PropertyChanging;

        public event PropertyChangedEventHandler PropertyChanged;

        protected virtual void SendPropertyChanging()
        {
            if (this.PropertyChanging != null)
                this.PropertyChanging(this, emptyChangingEventArgs);
        }

        protected virtual void SendPropertyChanging(System.String propertyName) 
        {
            if (this.PropertyChanging != null)
                this.PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
        }

        protected virtual void SendPropertyChanged(System.String propertyName)
        {
             if (this.PropertyChanged != null)
                 this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}

[njappboy]

Can I expect any timely follow-up on this?

[njappboy]

I ran the above code/database setup against an Oracle 10g Express Edition without any of the above issues.
If you notice in my first post I stated I'm using "Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production".

Does LinqToOracle support 9.2.0.6.0? Your requirements page, http://www.devart.com/dotconnect/oracle ... ml#require, states it supports Oracle server 9.2.0.4 or higher. I notice your requirements also say VS 2008 support. I'm developing with VS 2010, could that be causing an issue with the generator or the t4 templates that wouldn't be reported during compilation?

[StanislavK]

The error you are getting specifies that the Oracle server interprets one of parameter values as LONG instead of varchar2 for some reason. Could you please specify the following, so that we are able to analyze the issue in more details:
- whether you are connecting in the OCI or Direct mode (i.e., with or without Oracle client);
- the NLS settings (e.g., the result set of the 'select * from nls_database_parameters' query) of the server on which the problem occurs;
- whether you are able to execute this update statement with some standard tool and via 'plain' OracleCommand:

Code: Select all

OracleConnection con = (OracleConnection)dx.Connection;
con.Open();
OracleCommand cmd = new OracleCommand(
  "UPDATE DB_TESTING.TEST SET NAME2 = :p1 WHERE ID = :key1 AND NAME2 = :chk1", con);

cmd.Parameters.Add( new OracleParameter() 
  { ParameterName = "p1", DbType = DbType.String, Value = "Test 3" });

cmd.Parameters.Add(new OracleParameter() 
  { ParameterName = "key1", DbType = DbType.Decimal, Value = id });

cmd.Parameters.Add(new OracleParameter() 
  { ParameterName = "chk1", DbType = DbType.String, Value = "Test 2" });

cmd.ExecuteReader();

At the moment, we couldn't reproduce this issue on our 9.2.0.6 Oracle server.

As for the delay, we have a two business days response policy, though do our best to reply as soon as possible.

[njappboy]

- whether you are connecting in the OCI or Direct mode (i.e., with or without Oracle client);

with Oracle Client using this connection string:

Code: Select all

User Id=Db_Testing;Password=*****;Server=********;Home=oraclient11g_home2;Persist Security Info=True

- the NLS settings (e.g., the result set of the 'select * from nls_database_parameters' query) of the server on which the problem occurs;

• Parameter: Value
  NLS_CALENDAR: GREGORIAN
  NLS_CHARACTERSET: AL32UTF8
  NLS_COMP BINARY: BINARY
  NLS_CURRENCY: $
  NLS_DATE_FORMAT: DD-MON-RR
  NLS_DATE_LANGUAGE: AMERICAN
  NLS_DUAL_CURRENCY: $
  NLS_ISO_CURRENCY: AMERICA
  NLS_LANGUAGE: AMERICAN
  NLS_LENGTH_SEMANTICS: BYTE
  NLS_NCHAR_CHARACTERSET: AL16UTF16
  NLS_NCHAR_CONV_EXCP: FALSE
  NLS_NUMERIC_CHARACTERS: .,
  NLS_RDBMS_VERSION: 9.2.0.6.0
  NLS_SORT BINARY: BINARY
  NLS_TERRITORY: AMERICA
  NLS_TIMESTAMP_FORMAT: DD-MON-RR HH.MI.SSXFF AM
  NLS_TIMESTAMP_TZ_FORMAT: DD-MON-RR HH.MI.SSXFF AM TZR
  NLS_TIME_FORMAT: HH.MI.SSXFF AM
  NLS_TIME_TZ_FORMAT: HH.MI.SSXFF AM TZR

- whether you are able to execute this update statement with some standard tool and via 'plain' OracleCommand:

Code: Select all

OracleConnection con = (OracleConnection)dx.Connection;
con.Open();
OracleCommand cmd = new OracleCommand(
  "UPDATE DB_TESTING.TEST SET NAME2 = :p1 WHERE ID = :key1 AND NAME2 = :chk1", con);

cmd.Parameters.Add( new OracleParameter() 
  { ParameterName = "p1", DbType = DbType.String, Value = "Test 3" });

cmd.Parameters.Add(new OracleParameter() 
  { ParameterName = "key1", DbType = DbType.Decimal, Value = id });

cmd.Parameters.Add(new OracleParameter() 
  { ParameterName = "chk1", DbType = DbType.String, Value = "Test 2" });

cmd.ExecuteReader();

I ran this code, updating values appropriately in order for WHERE clause to be met. Execution throws an error.

Code: Select all

cmd.ExecuteReader(); // THROWS ERROR

Here is the Exception detail:
Message:ORA-01461: can bind a LONG value only for insert into a LONG column

StackTrace: at Devart.Data.Oracle.a0.b(Int32 A_0)
at Devart.Data.Oracle.a2.a(Int32 A_0, bj A_1)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Data.Oracle.OracleCommand.ExecuteReader()
at LinqToOracleSample1.Program.Main(String[] args) in C:\Users\cwagner\Documents\Visual Studio 2010\Projects\LinqToOracleSample1\LinqToOracleSample1\Program.cs:line 59
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()



Let me know if you need any other information in order to diagnosis this issue.

[StanislavK]

The issue may be caused by a problem in Oracle client/server compatibility. To check if this is the case, please try connecting in the Direct mode and via Oracle Client of version 9 or 10. Please tell us if the same command can be successfully executed in this case.

[njappboy]

This command works successfully when using the Direct Mode connection string.

Will devart be able to fix this issue with OCI connections? If so what kind of time frame are we looking at?

Code: Select all

OracleConnection con = (OracleConnection)dx.Connection; 
con.Open(); 
OracleCommand cmd = new OracleCommand( 
  "UPDATE DB_TESTING.TEST SET NAME2 = :p1 WHERE ID = :key1 AND NAME2 = :chk1", con); 

cmd.Parameters.Add( new OracleParameter() 
  { ParameterName = "p1", DbType = DbType.String, Value = "Test 3" }); 

cmd.Parameters.Add(new OracleParameter() 
  { ParameterName = "key1", DbType = DbType.Decimal, Value = id }); 

cmd.Parameters.Add(new OracleParameter() 
  { ParameterName = "chk1", DbType = DbType.String, Value = "Test 2" }); 

cmd.ExecuteReader(); 

[StanislavK]

Please try executing this command via some standard tool, like SQL*Plus. If the error occurs with a standard tool as well, the issue is most probably caused by a compatibility problem of Oracle client 11g and server of version 9. In this case, you can try using some other version of Oracle client, or address this issue to the Oracle support.

If the problem occurs only when dotConnect for Oracle is used, but not with a standard tool, please specify the exact version (including the minor version, the build number, and the capacity) of the Oracle client you are using and the operating system under which the issue occurs.