Oracle's OLEDB: VARCHAR2 encoding bug / problem

[Rob]

Hello,

VarCHAR2 values are not returned as Unicode values after
the first fetch in some queries that join tables using the
latest non-beta Oracle 9i Client and Oracle's OLEDB
provider.

We perform the following query "SELECT A.First_Name,
P.NT_USER_NAME FROM Account A, Person P WHERE P.Person_ID
= A.Person_ID" using OleDbCommand's ExecuteReader()
method.

We loop through the results and display the field values
in an edit box. The first fetch of rows (based on the
connection string FetchSize property) is fine. Any rows
after the initial fetch have VARCHAR2 encoding problems.
The VarCHAR2 values are not returned as Unicode values.
Each character is packed into a byte (i.e. character 1 is
in low byte of string[0], character 2 is in high byte of
string[0], character 3 is in low byte of string[1],
etc...).

Data is returned correctly when selecting these fields in
separate queries without joining the tables. Also, some
queries with joins work ok too, but the above one doesn't.

Below is sample code to produce problem and table
definitions. Sorry there is so much but I thought it may
be useful.

NOTE: The problem also happens with ODP.NET using the
command's fetchsize property.

using (OleDbConnection conn = new OleDbConnection
("Provider=OraOLEDB.Oracle;Password=xxx;Persist Security
Info=True;User ID=CANDI;Data Source=yyy;Extended
Properties=;OleDb.NET=true;FetchSize=5"))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand("SELECT A.First_Name,
P.NT_USER_NAME FROM Account A, Person P WHERE P.Person_ID
= A.Person_ID", conn);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
string val;
if (reader.IsDBNull(i))
val = "";
else
val = reader.GetValue(i).ToString();
resultsTxt.AppendText(String.Format("{0} ", val));
}

resultsTxt.AppendText("\r\n");
}
}

TABLE DEFINITIONS:
CREATE TABLE ACCOUNT
(
ACCOUNT_ID NUMBER(10) NOT NULL,
FIRST_NAME VARCHAR2(20) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
CANDI_USER_NAME VARCHAR2(50) NOT NULL,
ACCOUNT_TYPE_ID NUMBER(10),
CREATED DATE,
LAST_ACCESSED DATE,
DEPARTED DATE,
PERSON_ID NUMBER(10),
AGENT_ID NUMBER(10),
INSTRUMENT_ID NUMBER(10)
)
TABLESPACE CANDI_DATA_SMALL
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 2M
NEXT 2M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;


CREATE UNIQUE INDEX ACTOR_PK ON ACCOUNT
(ACCOUNT_ID)
LOGGING
TABLESPACE CANDI_INDEX_SMALL
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 2M
NEXT 2M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE ACCOUNT ADD (
CONSTRAINT ACTOR_PK PRIMARY KEY (ACCOUNT_ID)
USING INDEX
TABLESPACE CANDI_INDEX_SMALL
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 2M
NEXT 2M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
));


ALTER TABLE ACCOUNT ADD (
CONSTRAINT ACCOUNT_TYPE_ACCOUNT_FK1 FOREIGN KEY
(ACCOUNT_TYPE_ID)
REFERENCES ACCOUNT_TYPE (ACCOUNT_TYPE_ID));

ALTER TABLE ACCOUNT ADD (
CONSTRAINT AGENT_ACCOUNT_FK1 FOREIGN KEY (AGENT_ID)
REFERENCES AGENT (AGENT_ID));

ALTER TABLE ACCOUNT ADD (
CONSTRAINT INSTRUMENT_SYSTEM_ACCOUNT_FK1 FOREIGN KEY
(INSTRUMENT_ID)
REFERENCES INSTRUMENT_SYSTEM (INSTRUMENT_ID));

ALTER TABLE ACCOUNT ADD (
CONSTRAINT PERSON_ACCOUNT_FK1 FOREIGN KEY (PERSON_ID)
REFERENCES PERSON (PERSON_ID));


CREATE TABLE PERSON
(
PERSON_ID NUMBER(10) NOT NULL,
EMAIL_NAME VARCHAR2(50),
NT_USER_NAME VARCHAR2(20),
PHONE VARCHAR2(20),
SITE VARCHAR2(20)
)
TABLESPACE CANDI_DATA_SMALL
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 2M
NEXT 2M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;


CREATE UNIQUE INDEX PERSON_PK ON PERSON
(PERSON_ID)
LOGGING
TABLESPACE CANDI_INDEX_SMALL
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 2M
NEXT 2M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


ALTER TABLE PERSON ADD (
CONSTRAINT PERSON_PK PRIMARY KEY (PERSON_ID)
USING INDEX
TABLESPACE CANDI_INDEX_SMALL
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 2M
NEXT 2M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
));

Thank you for any help on this issue as it may prevent us
from using Oracle's data access technologies.

Thanks,
Rob