Bug when reading from table with CHAR Primary Key on 9i

Bug when reading from table with CHAR Primary Key on 9i

Postby simon.goodman » Tue 14 Jul 2015 09:25

Hi,

I am using dotConnect for Oracle v8.4.447 and have come across a problem that means I am unable to read from a table with a CHAR primary key. I am looking in to upgrading from our current version and this issue is currently stopping us from doing that.

I have included as much information as possible below, please let me know if you need any more details.

The issue appears under the following conditions:
    Connecting to a 9i database
    Using a direct connection
    Using a unicode connection
    One of the values being selected is the primary key
    The table being selected from is not empty

There is some C# code below that demonstrates the conditions under which this occurs. It expects that a 9i and 11g oracle server that are both set up with a schema created using the following script.

I have also tested a 10g and 12c database, neither appear to be affected by the issue. It only seems to affect 9i.

Code: Select all
DECLARE
  cnt NUMBER;
BEGIN
  SELECT COUNT(*)
   INTO cnt
    FROM all_users
  WHERE username = 'DEVART';
  IF (cnt>0) THEN
    EXECUTE IMMEDIATE 'DROP USER DEVART CASCADE';
  end if;
  end;
/
CREATE USER DEVART IDENTIFIED BY DEVART;
GRANT UNLIMITED TABLESPACE TO DEVART;
GRANT CREATE SESSION TO DEVART;

CREATE TABLE DEVART.INT0 (keycol INT PRIMARY KEY, datcol INT);
CREATE TABLE DEVART.INT1 (keycol INT PRIMARY KEY, datcol INT);
CREATE TABLE DEVART.INT2 (keycol INT PRIMARY KEY, datcol INT);

CREATE TABLE DEVART.CHAR0 (keycol CHAR(100) PRIMARY KEY, datcol INT);
CREATE TABLE DEVART.CHAR1 (keycol CHAR(100) PRIMARY KEY, datcol INT);
CREATE TABLE DEVART.CHAR2 (keycol CHAR(100) PRIMARY KEY, datcol INT);

INSERT INTO DEVART.INT1(keycol, datcol) VALUES (1,1);

INSERT INTO DEVART.INT2(keycol, datcol) VALUES (1,1);
INSERT INTO DEVART.INT2(keycol, datcol) VALUES (2,1);

INSERT INTO DEVART.CHAR1(keycol, datcol) VALUES ('1',1);

INSERT INTO DEVART.CHAR2(keycol, datcol) VALUES ('1',1);
INSERT INTO DEVART.CHAR2(keycol, datcol) VALUES ('2',1);

COMMIT;


Code: Select all
using System.Data.Common;
using Devart.Data.Oracle;
using NUnit.Framework;

namespace DevArtTests
{
    public class DevArtTests
    {
        private string GetHostname(int version)
        {
            return version == 9 ? "my.ninei.server" : "my.eleveng.server";
        }

        private string GetSid(int version)
        {
            return version == 9 ? "my.nine.sid" : "my.eleveng.sid";
        }

        private string GetConnectionString(string hostname, string sid, bool direct, bool unicode, string userId = "DEVART", string password = "DEVART")
        {
            var dataSource = string.Format(
                @"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT=1521)))(CONNECT_DATA=(SID={1})));",
                hostname,
                sid
            );

            if (direct)
            {
                dataSource = string.Format(
                    "Data Source={0};Port=1521;SID={1};",
                    hostname,
                    sid
                );
            }

            return string.Format(
                @"{0};Direct={1};User Id={2};Password='{3}';Connection Timeout=60;Pooling=false;Unicode={4};",
                dataSource,
                direct,
                userId,
                password,
                unicode
            );
        }

        [Test, Combinatorial]
        public void DevartUnknownError1(
            [Values(9, 11)] int version,
            [Values(true, false)] bool direct,
            [Values(true, false)] bool unicode,
            [Values("keycol", "datcol", "keycol, datcol")] string column,
            [Values("INT", "CHAR")] string keyType,
            [Values(0, 1, 2)] int rowCount)
        {
            var hostname = GetHostname(version);
            var sid = GetSid(version);
            var table = string.Format("DEVART.{0}{1}", keyType, rowCount);

            var connectionString = GetConnectionString(hostname, sid, direct, unicode);

            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();

                using (DbCommand command = connection.CreateCommand())
                {
                        command.CommandTimeout = 10; // Give up on hanging commands quickly
                        command.CommandText = "SELECT " + column + " FROM " + table;
                        var reader = command.ExecuteReader();

                        reader.Read(); // This is where the problems occur
                }
            }
        }       
    }
}


The call to reader.Read() fails with the error below when the test is called with the following parameters:
    9, True, True, "keycol", "CHAR", 2
Code: Select all
System.Exception : NET: Unknown error 1
   at Devart.Data.Oracle.DirectUtils.c(Int32 A_0)
   at Devart.Data.Oracle.h.a(Byte A_0)
   at Devart.Data.Oracle.db.b()
   at Devart.Data.Oracle.dg.d()
   at Devart.Data.Oracle.OracleInternalConnection.a(Boolean A_0)
   at Devart.Common.DbConnectionInternal.CloseInternalConnection()
   at Devart.Common.DbConnectionInternal.Close()
   at Devart.Data.Oracle.OracleInternalConnection.Close()
   at Devart.Common.DbConnectionBase.Close()
   at Devart.Common.DbConnectionBase.Dispose(Boolean disposing)
   at System.ComponentModel.Component.Dispose()
   at DevArtTests.DevArtTests.DevartUnknownError1(Int32 version, Boolean direct, Boolean unicode, String column, String keyType, Int32 rowCount) in DevArtTests.cs: line 73


The call to reader.Read() hangs and times out when the test is called with the following sets of parameters
    9, True, True, "keycol", "CHAR", 1
    9, True, True, "keycol, datcol", "CHAR", 1
    9, True, True, "keycol, datcol", "CHAR", 2
I receive the following error:
Code: Select all
Devart.Data.Oracle.OracleException : ORA-01013: user requested cancel of current operation
   at Devart.Data.Oracle.an.d()
   at Devart.Data.Oracle.cq.e()
   at Devart.Data.Oracle.cq.a()
   at Devart.Data.Oracle.i.b(Int32 A_0)
   at Devart.Data.Oracle.OracleDataReader.Read()
   at DevArtTests.DevArtTests.DevartUnknownError1(Int32 version, Boolean direct, Boolean unicode, String column, String keyType, Int32 rowCount) in DevArtTests.cs: line 71


Oracle 9i Version Information:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
simon.goodman
 
Posts: 2
Joined: Tue 14 Jul 2015 08:14

Re: Bug when reading from table with CHAR Primary Key on 9i

Postby Pinturiccio » Fri 17 Jul 2015 08:14

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Pinturiccio
Devart Team
 
Posts: 1954
Joined: Wed 02 Nov 2011 09:44

Re: Bug when reading from table with CHAR Primary Key on 9i

Postby simon.goodman » Wed 29 Jul 2015 09:33

I'm glad you were able to reproduce the issue with the information I provided.

It would be great if you can give me an update on how your investigation is going. Do you think it is likely that the issue will be fixed in upcoming release?

Thanks,
Simon.
simon.goodman
 
Posts: 2
Joined: Tue 14 Jul 2015 08:14

Re: Bug when reading from table with CHAR Primary Key on 9i

Postby Shalex » Fri 31 Jul 2015 11:46

The investigation is in progress. As soon as we have any results, we will let you know.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle