ORA-01406: fetched column value was truncated

[Alladin]

Hi, i'm getting following exception by using OracleDataReader in Unicode mode on Windows XP SP2 machine with 11.1 instant client & 10.2 Database Server...

at Devart.Data.Oracle.an.b(Int32 A_0)
at Devart.Data.Oracle.ap.e(Int32 A_0)
at Devart.Data.Oracle.ap.c(Int32 A_0)
at Devart.Data.Oracle.OracleDataReader.Read()

Sure that obfuscated call stack will help you to locate and fix the problem faster

[Alladin]

Problem was caused by using non-Unicode connection on Unicode database...

Grrrr....

When do you guys will detect Unicode mode automatically?

[Shalex]

The Unicode property defines the charset that will be used at network level for string transferring. The choice of this option has influence on the amount of conversions in the program (some performance penalties) and the possibility of data loss. If the Unicode property is set to true, UTF8 is used at the network level. Otherwise, the charset from regional settings of Windows is used. So, we recommend not to turn on the Unicode mode if there is no need of using it.

[Alladin]

How do I know when to turn Unicode on or off before actually making the connection?

[Shalex]

The Unicode property defines the charset that will be used at the network level for string transferring. The choice of this option has influence on the amount of conversions in the program and the possibility of data loss. If the Unicode property is set to true, UTF8 is used at the network level. Otherwise, the charset from regional settings of Windows is used.
a) If Unicode=true, the following scheme is implemented:
UTF8 (stored at the server) -> UTF8 (transported through the network) -> UTF16 (string in .NET).
b) If Unicode=true:
UTF16 -> UTF8 -> UTF16. Unfortunately, UTF16 can not be used at the network level.
c) If Unicode=false:
WE8MSWIN1252->WE8MSWIN1252->UTF16.

[Alladin]

The problem is following:

1) I don't know whether server uses UTF8/UTF16 encoding internally before actually making a connection to the server and quering some database parameters.

2) If server is Unicode (UTF8/UTF16), but connection string doesn't have Unicode=true, you get various ORA-XXX errors all over unexpected places.

3) If server is not Unicode, but connection string says Unicode=true, you get some performance penalties, right?

Why cant OracleConnection determine Unicode server status automatically on Open() and protect lazy programmers from stupid ORA-01406 mistakes?

[Shalex]

1. You can execute the select * from nls_database_parameters where parameter='NLS_CHARACTERSET' statement and look up its value to determine whether your database uses Unicode or not.
2. The reason of the error can be data losses during string conversions described above.
3. Yes, some performance penalties take place if Unicode=true.

In our tools, we try to implement as much functionalty features as possible. But we think that automatical setting change is not the best approach.

[ahcheong]

My oracle database not unicode
NLS_CHARACTERSET WE8MSWIN1252

but i faced same ORA-01406 while using linq to retrieve data.

[MariiaI]

Please provide us with the following information:
- the version of dotConnect for Oracle/LinqConnect;
- the Oracle server version;
- the mode you are working with - direct or OCI;
- the DDL/DML scripts for the necessary database table;
- the code snippet and the generated SQL query: https://www.devart.com/dotconnect/oracl ... nitor.html , etc.;

If possible, please create a small test project, with which this error could be reproduced, and send it us, so that we are able to investigate it and find a solution for you.