ORA-01406: fetched column value was truncated

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Alladin
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

ORA-01406: fetched column value was truncated

Post by Alladin » Tue 27 Oct 2009 11:12

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
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Post by Alladin » Tue 27 Oct 2009 20:57

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

Grrrr....

When do you guys will detect Unicode mode automatically?

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 28 Oct 2009 11:09

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
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Post by Alladin » Fri 06 Nov 2009 10:57

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

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 11 Nov 2009 12:04

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
Posts: 149
Joined: Mon 27 Nov 2006 16:18
Contact:

Post by Alladin » Fri 13 Nov 2009 15:16

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
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 16 Nov 2009 12:44

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
Posts: 2
Joined: Fri 17 Oct 2014 11:03

Re: ORA-01406: fetched column value was truncated

Post by ahcheong » Fri 27 Feb 2015 08:21

My oracle database not unicode
NLS_CHARACTERSET WE8MSWIN1252

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

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: ORA-01406: fetched column value was truncated

Post by MariiaI » Fri 27 Feb 2015 11:57

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.

Post Reply