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

dvpnello · Post by **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!

StanislavK · Post by **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.

dvpnello · Post by **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!

StanislavK · Post by **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.

davide_lenza · Post by **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

StanislavK · Post by **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.

dvpnello · Post by **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'.

StanislavK · Post by **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.

dvpnello · Post by **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

StanislavK · Post by **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).

dvpnello · Post by **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,

Devart Forums

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

Missing information

It is working with standard tool