ORA-01400 for 6.5 on Oracle 10gEE 10.2.0.4.0 64bit

ORA-01400 for 6.5 on Oracle 10gEE 10.2.0.4.0 64bit

Postby dvpnello » Thu 13 Oct 2011 15:27

Hello,

We have an application developed on 5.35.54.0 that is working correctly on different Oracle instances using Direct Mode:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
Oracle Database 10g Express Edition Release 10.2.0.1.0 32bit


The recent upgrade to the latest version (6.50.228.0) made the application not working anymore on the 10.2.0.4 (64bit) instance.
The same application is still working fine on the XE Oracle DB

Here's how the problem can be reproduced:

Code: Select all
using System;
using System.Collections.Generic;
using System.Linq;
using ONE2ONEXEContext;

/*
CREATE TABLE ONE2ONE.MATERIAL
(
  BORG                   NVARCHAR2(10),
  MATERIAL               NVARCHAR2(20),
  MATERIAL_DESCR         NVARCHAR2(50),
  MATERIAL_DESCR_LONG    NVARCHAR2(50),
  MATERIAL_DESCR_HTML    NVARCHAR2(500),
  MATERIAL_GROUP         NVARCHAR2(10),
  MATERIAL_STATUS_B2B    NVARCHAR2(10),
  MATERIAL_STATUS_B2C    NVARCHAR2(10),
  HYBRID                 NVARCHAR2(20),
  ACTIVE_B2B_FL          NUMBER(1),
  ACTIVE_B2C_FL          NUMBER(1),
  ORDERABLE_B2B_FL       NUMBER(1),
  ORDERABLE_B2C_FL       NUMBER(1),
  SORTORDER_B2B          NVARCHAR2(20),
  SORTORDER_B2C          NVARCHAR2(20),
  UNITS_AVAIL_TOTAL_B2B  NUMBER(15,3),
  UNITS_AVAIL_LEFT_B2B   NUMBER(15,3),
  UNITS_AVAIL_TOTAL_B2C  NUMBER(15,3),
  UNITS_AVAIL_LEFT_B2C   NUMBER(15,3),
  LOCAL_USAGE_1          NVARCHAR2(25),
  LOCAL_USAGE_2          NVARCHAR2(25),
  LOCAL_USAGE_3          NVARCHAR2(25),
  ADD_DT                 DATE,
  ADD_NM                 NVARCHAR2(50),
  LAST_MAINT_DT          DATE,
  LAST_MAINT_NM          NVARCHAR2(50),
  RECORD_STATUS_CD       NVARCHAR2(1)           DEFAULT 'A'
);
*/

namespace O2OOracleTest
{
    class Program
    {
        static void Main(string[] args)
        {
            ONE2ONEXEDataContext context = new ONE2ONEXEDataContext();
            List dmList = (from t in context.MATERIALs
                                     where t.BORG == "borg1" &&
                                     t.MATERIAL1 == "mat1"
                                     select t).ToList();
           
            if (dmList != null && dmList.Count > 0)
            {
                MATERIAL dm = dmList.First();
                context.MATERIALs.DeleteOnSubmit(dm);
                context.SubmitChanges();
            }
            MATERIAL insMat = new MATERIAL();
            insMat.MATERIAL1 = "mat1";
            insMat.BORG = "borg1";

            context.MATERIALs.InsertOnSubmit(insMat);
            context.SubmitChanges();
            context.Dispose();
        }
    }
}


This generates the following error in inserting the MATERIAL record

ORA-01400: cannot insert NULL into ("ONE2ONE"."MATERIAL"."MATERIAL")

when the backend DB is Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit.
This works fine for the XE instance.
Many thanks!
dvpnello
 
Posts: 7
Joined: Thu 13 Oct 2011 13:54

Postby StanislavK » Mon 17 Oct 2011 15:14

Could you please specify the following, so that we are able to analyze the issue in more details:
- if there is any difference between the configurations of DataContexts you use for these servers (for example, do mapping sources you use differ or are the same for 10.2.0.4 and 10.2.0.1 servers?);
- the SQL commands generated for the problem code snippet;
- how is the primary key of the Material entity class defined (as far as I can understand from the script, the table itself has no primary key).

If possible, please send us the model with which the problem occurs, or a complete small sample with which the issue can be reproduced.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Missing information

Postby dvpnello » Mon 17 Oct 2011 16:24

- There are no changes in the configuration of DataContexts apart from the server information and user credentials

- Here's the SQL
Code: Select all
INSERT INTO ONE2ONE.MATERIAL (BORG, MATERIAL, ACTIVE_B2B_FL) VALUES (:p1, :p2, :p3)
-- p1: Input NVarChar (Size = 5; DbType = String) [borg1]
-- p2: Input NVarChar (Size = 4; DbType = String) [mat1]
-- p3: Input Number (Size = 0; DbType = Decimal) []
-- Context: Devart.Data.Oracle.Linq.Provider.OracleDataProvider Model: an Build: 3.0.5.0
ORA-01400: cannot insert NULL into ("ONE2ONE"."MATERIAL"."MATERIAL")


- I missed the primary key definitions in the source code comments. Here's the complete SQL script (BORG, MATERIAL are the PKs)
Code: Select all
CREATE TABLE ONE2ONE.MATERIAL
(
  BORG                   NVARCHAR2(10),
  MATERIAL               NVARCHAR2(20),
  MATERIAL_DESCR         NVARCHAR2(50),
  MATERIAL_DESCR_LONG    NVARCHAR2(50),
  MATERIAL_DESCR_HTML    NVARCHAR2(500),
  MATERIAL_GROUP         NVARCHAR2(10),
  MATERIAL_STATUS_B2B    NVARCHAR2(10),
  MATERIAL_STATUS_B2C    NVARCHAR2(10),
  HYBRID                 NVARCHAR2(20),
  ACTIVE_B2B_FL          NUMBER(1),
  ACTIVE_B2C_FL          NUMBER(1),
  ORDERABLE_B2B_FL       NUMBER(1),
  ORDERABLE_B2C_FL       NUMBER(1),
  SORTORDER_B2B          NVARCHAR2(20),
  SORTORDER_B2C          NVARCHAR2(20),
  UNITS_AVAIL_TOTAL_B2B  NUMBER(15,3),
  UNITS_AVAIL_LEFT_B2B   NUMBER(15,3),
  UNITS_AVAIL_TOTAL_B2C  NUMBER(15,3),
  UNITS_AVAIL_LEFT_B2C   NUMBER(15,3),
  LOCAL_USAGE_1          NVARCHAR2(25),
  LOCAL_USAGE_2          NVARCHAR2(25),
  LOCAL_USAGE_3          NVARCHAR2(25),
  ADD_DT                 DATE,
  ADD_NM                 NVARCHAR2(50),
  LAST_MAINT_DT          DATE,
  LAST_MAINT_NM          NVARCHAR2(50),
  RECORD_STATUS_CD       NVARCHAR2(1)           DEFAULT 'A'
);

ALTER TABLE ONE2ONE.MATERIAL ADD (
  CONSTRAINT MATERIAL_PK PRIMARY KEY (BORG, MATERIAL));


In order to localize the issue, the Material class contains only the PKs (BORG, MATERIAL) plus ACTIVE_B2B_FLAG. It does not include all columns defined in the MATERIAL table.
Anyway, I'm going to send you the complete example.
Thank you!
dvpnello
 
Posts: 7
Joined: Thu 13 Oct 2011 13:54

Postby StanislavK » Wed 19 Oct 2011 11:34

Thank you for the sample application. Could you please try executing the same insert command via 'plain' ADO.NET (i.e., with the OracleCommand class) or via some standard tool? Please tell us about the results. At the moment, we couldn't reproduce the problem in our environment.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

It is working with standard tool

Postby davide_lenza » Wed 19 Oct 2011 14:36

Hi
I am a colleague of dvpnello.
The insert via Aqua data studio or LinqPad is working properly.
We are using VisualStudio v10.0.40219.1 SP1Rel
The target framework is .NET Framework 4. The Platform target is X86
Bye
davide_lenza
 
Posts: 5
Joined: Thu 05 Mar 2009 13:19

Postby StanislavK » Thu 20 Oct 2011 12:52

Please also try executing this command via the OracleCommand object of dotConnect for Oracle. Please run the following:
Code: Select all
OracleConnection conn = (OracleConnection)context.Connection;
conn.Open();
OracleCommand cmd = new OracleCommand(
  "INSERT INTO MATERIAL (BORG, MATERIAL, ACTIVE_B2B_FL) VALUES (:p1, :p2, :p3)",
  conn);
cmd.Parameters.Add(new OracleParameter("p1", "borg1"));
cmd.Parameters.Add(new OracleParameter("p2", "mat1"));
cmd.Parameters.Add(new OracleParameter(
  "p3",
  OracleDbType.Number,
  DBNull.Value,
  System.Data.ParameterDirection.Input));
cmd.ExecuteNonQuery();

and tell us if the problem occurs in this case. Also, if possible, please check whether this issue can be reproduced in the OCI connection mode.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby dvpnello » Thu 20 Oct 2011 16:17

Hello,

The code that you posted is inserting the record correctly. I added just the schema name in front of the table one.

Code: Select all
OracleConnection conn = (OracleConnection)context.Connection;
conn.Open();
OracleCommand cmd = new OracleCommand(
  "INSERT INTO ONE2ONE.MATERIAL (BORG, MATERIAL, ACTIVE_B2B_FL) VALUES (:p1, :p2, :p3)",
  conn);
cmd.Parameters.Add(new OracleParameter("p1", "borg1"));
cmd.Parameters.Add(new OracleParameter("p2", "mat1"));
cmd.Parameters.Add(new OracleParameter(
  "p3",
  OracleDbType.Number,
  DBNull.Value,
  System.Data.ParameterDirection.Input));
cmd.ExecuteNonQuery();


Through dbMonitor I noticed that p1 and p2 are treated as 'VarChar' in the OracleCommand example, while in the previous code those were mapped to 'NVarChar'.
dvpnello
 
Posts: 7
Joined: Thu 13 Oct 2011 13:54

Postby StanislavK » Fri 21 Oct 2011 08:54

Thank you for your assistance, we have reproduced the issue. We will inform you as soon as it is fixed.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby dvpnello » Thu 17 Nov 2011 15:46

Hello,
I wonder whether the 6.50.244 build fixed this issue.
In case not, I'm willing to test any alfa or beta release that corrects the problem.

Thanks
dvpnello
 
Posts: 7
Joined: Thu 13 Oct 2011 13:54

Postby StanislavK » Tue 22 Nov 2011 18:00

Sorry for the delay. Yes, the 6.50.244 build of dotConnect for Oracle should include the fix for this issue. You can download it from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (provided that you have an active subscription).
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby dvpnello » Tue 29 Nov 2011 15:33

Hello,

Both versions 6.50.244 and 6.50.250 fixes the issue. However, those generates other problems that are independent from the DB instance. I'm going to open a new ticket for those.
Thanks,
dvpnello
 
Posts: 7
Joined: Thu 13 Oct 2011 13:54


Return to dotConnect for Oracle