ORA-01406: fetched column value was truncated

ORA-01406: fetched column value was truncated

Postby 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

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

Postby 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.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

Postby Alladin » Fri 06 Nov 2009 10:57

How do I know when to turn Unicode on or off before actually making the connection?
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby 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.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

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

Postby 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.
Shalex
Devart Team
 
Posts: 7705
Joined: Thu 14 Aug 2008 12:44

Re: ORA-01406: fetched column value was truncated

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

Re: ORA-01406: fetched column value was truncated

Postby 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/oracle/docs/DbMonitor.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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for Oracle