Invalid Characters (\0s) Returned by OracleDataReader

[RobertoP]

Hello all,

I am trying to create an simple application that communicates with an Oracle
DB, but the data returned by the OracleDataReader object contains random
invalid characters.

To be more Specific, it replaces some characters with a '\0'

Example: Where it should be NIC it returns sometimes N\0\0

This is what I am using in my code:

//////

this.DWConnection.Open();

OracleCommand oraCMD = new OracleCommand("Select * from myTable where
(wname = 'F18104854')", this.DWConnection);

OracleDataReader myResultsReader = oraCMD.ExecuteReader();

while(myResultsReader.Read())
{
myResultsReader.GetValues(values);
}

this.DWConnection.Close();

//////

Some things I tried:

*I checked the variable "values" above, and many rows, not always the same
column, has \0 instead of characters. What is interesting is that it has the
same number of \0s as the actual characters in Oracle. Also, it is always in
the end of the string, and the first characters are read correctly.
Ex.: N\0\0 instead of NIC, 18852\0\0\0\0\0 instead of 188524C-01

*Seems not to have any pattern to when it replaces the characters with \0s
sometimes it is every other 2 rows, or every other 3, and so on.

*I tried the same code using OracleAdapter, and same results

*Using the server explorer, or the query builder tool, and the option to
view data at development time works just fine.

Any Ideas?

Thanks,

RobertoP

[Nick Malik [Microsoft]]

four nearly identical posts in twenty minutes. That has to be some kind of
record.

Can you post a short but complete code segment that illustrates the problem?
The code you have looks fine.
The fact that the dataadapter returns the same things is concerning, because
very little of your code would have been involved.
that would point to either a defect or a configuration problem in either
your Oracle driver or the way the application is using .Net.

I'm still hoping we can find it in the code... that's why I've asked for
something a little more complete. Note: I don't have Oracle to test
against, so I'm hoping to help you spot it in the code.
--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"RobertoP" <Ro******@discussions.microsoft.com> wrote in message
news:66**********************************@microsof t.com...

Hello all,

I am trying to create an simple application that communicates with an Oracle DB, but the data returned by the OracleDataReader object contains random
invalid characters.

To be more Specific, it replaces some characters with a '\0'

Example: Where it should be NIC it returns sometimes N\0\0

This is what I am using in my code:

//////

this.DWConnection.Open();

OracleCommand oraCMD = new OracleCommand("Select * from myTable where
(wname = 'F18104854')", this.DWConnection);

OracleDataReader myResultsReader = oraCMD.ExecuteReader();

while(myResultsReader.Read())
{
myResultsReader.GetValues(values);
}

this.DWConnection.Close();

//////

Some things I tried:

*I checked the variable "values" above, and many rows, not always the same
column, has \0 instead of characters. What is interesting is that it has the same number of \0s as the actual characters in Oracle. Also, it is always in the end of the string, and the first characters are read correctly.
Ex.: N\0\0 instead of NIC, 18852\0\0\0\0\0 instead of 188524C-01

*Seems not to have any pattern to when it replaces the characters with \0s
sometimes it is every other 2 rows, or every other 3, and so on.

*I tried the same code using OracleAdapter, and same results

*Using the server explorer, or the query builder tool, and the option to
view data at development time works just fine.

Any Ideas?

Thanks,

RobertoP

[Brian Brown]

Nick and Roberto,

I have Oracle to test it against and I would be glad to help. I agree with
Nick the code looks fine. I use the OracleClient nearly every day and I have
not seen anything like this. When you post your sample can you also include
how the table is setup (fields, datatypes, etc.) and what you are using to
pull back the data (e.g. stored procedures, ad-hoc queries, etc...)?

[RobertoP]

I am sorry for the repeated posts, but I received an errors when I first
tried to post...

This is a long email, and I tried to put the part of the code that is
filling my dataGrid control. I also have a Connection Control, and a DS
Control on my form.

Oracle Server Version: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit
Production

This is the Table Structure, in fact it is a view.

LOAD_DATE DATE
SITE VARCHAR2(4)
TEST_TRANSACTION_ID NUMBER
TEST_CODE VARCHAR2(450)
SERIAL_NUMBER VARCHAR2(450)
FM_OPERATION_SEQ_NUM NUMBER
OPERATION_SEQ_NUM NUMBER
TEST_RESULT NUMBER
DATE_TESTED DATE
FAILURE_ID VARCHAR2(450)
OPERATOR_ID NUMBER
AMBIENT_TEMPERATURE NUMBER
HUMIDITY NUMBER
STATION_ID VARCHAR2(450)
TEST_DURATION NUMBER
CERTIFICATE_NUM NUMBER
DRIVER_VERSION VARCHAR2(450)
SEQUENCE_FILE_VERSION VARCHAR2(450)
NUM_INSTRUMENTS NUMBER
ERROR_CODE NUMBER
ERROR_MESSAGE VARCHAR2(450)
ATTRIBUTE1 VARCHAR2(450)
ATTRIBUTE2 VARCHAR2(450)
ATTRIBUTE3 VARCHAR2(450)
ATTRIBUTE4 VARCHAR2(450)
ATTRIBUTE5 VARCHAR2(450)
ATTRIBUTE6 VARCHAR2(450)
ATTRIBUTE7 VARCHAR2(450)
ATTRIBUTE8 VARCHAR2(450)
ATTRIBUTE9 VARCHAR2(450)
ATTRIBUTE10 VARCHAR2(450)
ULTIMATE_VERSION NUMBER
TR_CREATION_DATE DATE
PART_NUMBER VARCHAR2(40)
DESCRIPTION VARCHAR2(450)
INVENTORY_ITEM_ID NUMBER
INVENTORY_ITEM_STATUS_CODE VARCHAR2(450)
ORGANIZATION_ID NUMBER
PLANNER_CODE VARCHAR2(450)
PLANNER_DESC VARCHAR2(450)
WIP_ENTITY_ID NUMBER
WIP_ENTITY_NAME VARCHAR2(450)
ENTITY_TYPE NUMBER
WE_DESCRIPTION VARCHAR2(450)
WDJ_STATUS_TYPE NUMBER
SCHEDULED_START_DATE DATE
DATE_RELEASED DATE
DATE_COMPLETED DATE
DATE_CLOSED DATE
START_QUANTITY NUMBER
QUANTITY_COMPLETED NUMBER
QUANTITY_SCRAPPED NUMBER
CLASS_CODE VARCHAR2(450)
JOB_TYPE VARCHAR2(450)
PRODUCT_LINE VARCHAR2(450)
PRODUCT_GROUP VARCHAR2(450)
PRODUCT_FAMILY VARCHAR2(450)
PRODUCT_SUBFAMILY VARCHAR2(450)
BOOKING_CLASS VARCHAR2(450)
OPERATOR_NAME VARCHAR2(450)
PASS NUMBER
OVERALL_PASS NUMBER
ORIG_SHIP_DATE DATE
REPAIR_RETURN_DATE DATE
WARRANTY NUMBER
QTY_TESTED NUMBER
QTY_ACCEPTED NUMBER
QTY_REJECTED NUMBER
QTY_REPAIRED NUMBER
JOB_INFO VARCHAR2(450)

And this is my Code:

private void B_SelectData_Click(object sender, System.EventArgs e)
{
string mStatement = "Select * from nidw_mfg_test_results_all_v where
wip_entity_name = 'F188524'"

try
{
this.Cursor = Cursors.WaitCursor;
this.DWConnection.Open();

//Retrieve Number of results
OracleCommand oraCMD = new OracleCommand(mStatement, this.DWConnection);

///////////////////////////
//Solution using DataReader
///////////////////////////

OracleDataReader myResultsReader = oraCMD.ExecuteReader();

DataTable schemaTable = myResultsReader.GetSchemaTable();
DataTable mDataTable = new DataTable();

mDataTable.TableName = "nidw_mfg_test_results_all_v";

int i = 0;

for(i=0;i < schemaTable.Rows.Count;i++)
{
mDataTable.Columns.Add(myResultsReader.GetName(i),
myResultsReader.GetFieldType(i));
}

//Retrieve Values
mDataTable.BeginLoadData();

object[] values = new object[schemaTable.Rows.Count];

while(myResultsReader.Read())
{
myResultsReader.GetValues(values);
mDataTable.LoadDataRow(values, true);

}

mDataTable.EndLoadData();

////Fill Dataset
this.DS_Results.Tables.Add(mDataTable);

///Fill Datagrid
this.DG_Results.DataSource =
this.DS_Results.Tables["nidw_mfg_test_results_all_v"];
}
finally
{
this.DWConnection.Close();
this.Cursor = Cursors.Arrow;

}
}

[Jon Skeet [C# MVP]]

RobertoP <Ro******@discussions.microsoft.com> wrote:

I am sorry for the repeated posts, but I received an errors when I first
tried to post...

This is a long email, and I tried to put the part of the code that is
filling my dataGrid control. I also have a Connection Control, and a DS
Control on my form.

I would suggest trying to reproduce the problem with a test database
and test code - then posting a complete program as Nick said before.

See http://www.pobox.com/~skeet/csharp/complete.html for hints about
that.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Brian Brown]

Roberto,

I have tested the code that you provided with an Oracle table defined
exactly as you specified and see no problems. My test code works fine. I
would guess that the problem lies elsewhere in your code or with the
connection to Oracle (e.g. driver, Oracle Client Installation, etc...)

For a problem of this nature I agree with Jon and Nick you should see if you
can reproduce the behavior with a test program with a test database. If you
can then post the complete code (and table structure) here so we can help
diagnose the problem.

Hope this helps.
----------

[Nick Malik [Microsoft]]

Hello Roberto,

I looked carefully through the code. I don't see anything that would cause
it to fail.
While your code is doing the work of the DataAdapter (to create a DataTable
object and fill it with values), it appears sound.

You say that this problem happens if you use a DataAdapter as well?

If you point this at a SQL Server database, do you get the strange data? If
not, then you have factored out the code as the issue... it would have to be
the driver, the connection, or the database itself. Since you mention that
Visual Studio is able to see the data during design time, then it may have
something to do with the OLEDB driver or the connection settings that you
are applying.

Truly mysterious. Have you also posted this problem to an Oracle-oriented
newsgroup? Perhaps someone there has had a similar problem specifically
with Oracle.

Sorry to pepper you with questions. I'm running out of good ideas, and I'm
starting to wade into some not-so-good ones.
--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"RobertoP" <Ro******@discussions.microsoft.com> wrote in message
news:46**********************************@microsof t.com...

I am sorry for the repeated posts, but I received an errors when I first
tried to post...

This is a long email, and I tried to put the part of the code that is
filling my dataGrid control. I also have a Connection Control, and a DS
Control on my form.

Oracle Server Version: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

This is the Table Structure, in fact it is a view.

LOAD_DATE DATE
SITE VARCHAR2(4) TEST_TRANSACTION_ID NUMBER
TEST_CODE VARCHAR2(450) SERIAL_NUMBER VARCHAR2(450) FM_OPERATION_SEQ_NUM NUMBER
OPERATION_SEQ_NUM NUMBER
TEST_RESULT NUMBER
DATE_TESTED DATE
FAILURE_ID VARCHAR2(450) OPERATOR_ID NUMBER
AMBIENT_TEMPERATURE NUMBER
HUMIDITY NUMBER
STATION_ID VARCHAR2(450) TEST_DURATION NUMBER
CERTIFICATE_NUM NUMBER
DRIVER_VERSION VARCHAR2(450) SEQUENCE_FILE_VERSION VARCHAR2(450) NUM_INSTRUMENTS NUMBER
ERROR_CODE NUMBER
ERROR_MESSAGE VARCHAR2(450) ATTRIBUTE1 VARCHAR2(450) ATTRIBUTE2 VARCHAR2(450) ATTRIBUTE3 VARCHAR2(450) ATTRIBUTE4 VARCHAR2(450) ATTRIBUTE5 VARCHAR2(450) ATTRIBUTE6 VARCHAR2(450) ATTRIBUTE7 VARCHAR2(450) ATTRIBUTE8 VARCHAR2(450) ATTRIBUTE9 VARCHAR2(450) ATTRIBUTE10 VARCHAR2(450) ULTIMATE_VERSION NUMBER
TR_CREATION_DATE DATE
PART_NUMBER VARCHAR2(40) DESCRIPTION VARCHAR2(450) INVENTORY_ITEM_ID NUMBER
INVENTORY_ITEM_STATUS_CODE VARCHAR2(450) ORGANIZATION_ID NUMBER
PLANNER_CODE VARCHAR2(450) PLANNER_DESC VARCHAR2(450) WIP_ENTITY_ID NUMBER
WIP_ENTITY_NAME VARCHAR2(450) ENTITY_TYPE NUMBER
WE_DESCRIPTION VARCHAR2(450) WDJ_STATUS_TYPE NUMBER
SCHEDULED_START_DATE DATE
DATE_RELEASED DATE
DATE_COMPLETED DATE
DATE_CLOSED DATE
START_QUANTITY NUMBER
QUANTITY_COMPLETED NUMBER
QUANTITY_SCRAPPED NUMBER
CLASS_CODE VARCHAR2(450) JOB_TYPE VARCHAR2(450) PRODUCT_LINE VARCHAR2(450) PRODUCT_GROUP VARCHAR2(450) PRODUCT_FAMILY VARCHAR2(450) PRODUCT_SUBFAMILY VARCHAR2(450) BOOKING_CLASS VARCHAR2(450) OPERATOR_NAME VARCHAR2(450) PASS NUMBER
OVERALL_PASS NUMBER
ORIG_SHIP_DATE DATE
REPAIR_RETURN_DATE DATE
WARRANTY NUMBER
QTY_TESTED NUMBER
QTY_ACCEPTED NUMBER
QTY_REJECTED NUMBER
QTY_REPAIRED NUMBER
JOB_INFO VARCHAR2(450)
And this is my Code:

private void B_SelectData_Click(object sender, System.EventArgs e)
{
string mStatement = "Select * from nidw_mfg_test_results_all_v where
wip_entity_name = 'F188524'"

try
{
this.Cursor = Cursors.WaitCursor;
this.DWConnection.Open();

//Retrieve Number of results
OracleCommand oraCMD = new OracleCommand(mStatement, this.DWConnection);

///////////////////////////
//Solution using DataReader
///////////////////////////

OracleDataReader myResultsReader = oraCMD.ExecuteReader();

DataTable schemaTable = myResultsReader.GetSchemaTable();
DataTable mDataTable = new DataTable();

mDataTable.TableName = "nidw_mfg_test_results_all_v";

int i = 0;

for(i=0;i < schemaTable.Rows.Count;i++)
{
mDataTable.Columns.Add(myResultsReader.GetName(i),
myResultsReader.GetFieldType(i));
}

//Retrieve Values
mDataTable.BeginLoadData();

object[] values = new object[schemaTable.Rows.Count];

while(myResultsReader.Read())
{
myResultsReader.GetValues(values);
mDataTable.LoadDataRow(values, true);

}

mDataTable.EndLoadData();

////Fill Dataset
this.DS_Results.Tables.Add(mDataTable);

///Fill Datagrid
this.DG_Results.DataSource =
this.DS_Results.Tables["nidw_mfg_test_results_all_v"];
}
finally
{
this.DWConnection.Close();
this.Cursor = Cursors.Arrow;

}
}

[RobertoP]

Hey guys,

I am really impressed with the quick answers and the level of efforts you
guys put together to help me! Thanks!

It still did not work, and I will follow your advise to try to create a
smaller, test DB, and Test Program that reproduces the problem. I will also
try on a different computer as well.

Just FYI, I tried to reduce the Select statement to just one VARCHAR2 column
and then all the rows, except the very first one had the wrong values.

"Brian Brown" wrote:

Roberto,

I have tested the code that you provided with an Oracle table defined
exactly as you specified and see no problems. My test code works fine. I
would guess that the problem lies elsewhere in your code or with the
connection to Oracle (e.g. driver, Oracle Client Installation, etc...)

For a problem of this nature I agree with Jon and Nick you should see if you
can reproduce the behavior with a test program with a test database. If you
can then post the complete code (and table structure) here so we can help
diagnose the problem.

Hope this helps.
----------