ORA-01406 while building a Sphinx index

Question

We are getting ORA-01406 error while trying to build an index over Oracle DB using Sphinx:

root@bdt:~# ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/ NLS_LANG=AMERICAN_AMERICA.UTF8 indexer bsrpIndex --rotate                                                                            
Sphinx 2.2.6-id64-release (r4843)
Copyright (c) 2001-2014, Andrew Aksyonoff
Copyright (c) 2008-2014, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinxsearch/sphinx.conf'...
indexing index 'bsrpIndex'...
ERROR: index 'bsrpIndex': sql_fetch_row: [Oracle][ODBC][Ora]ORA-01406: fetched column value was truncated
.
total 648 docs, 16082835 bytes
total 18.330 sec, 877377 bytes/sec, 35.35 docs/sec
total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 0 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
root@bdt:~# 

We are using Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production. We understand that this error relates to the buffer size on client side but we are not sure how to configure it. We are using the ODBC driver. Basically, two fields that prevent the Sphinx index to be created are of VARCHAR2(4000) and VARCHAR(100) types. If we remove them from the query in Sphinx config the index builds without an error.

So, the question is: what can we do to extend this client side buffer to handle big columns data properly?

Answer 1

I resolved the very same error by changing:

NLS_LANG=AMERICAN_AMERICA.UTF8

to

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

After this i got another subsequent error:

WARNING: '...' column truncated (buffer=..., got=...); consider revising sql_column_buffers

I've fixed it as described in Sphinx Documentation