Oracle Connector Stage - ORA 1406

[Dmitriy]

Hello!
We have DS 8.5.

I've faced with the followig problem using the Oracle Connector Stage in DS 8.5:

When I try to make a "veiw data" in the Oracle Stage from a field (varchar2 30) with cyrillic characters, i am getting the folowing error message:

"While reading data for column XX, the connector received Oracle error code ORA-1406"

I have noticed that when I use a SUBSTR() in my seelct to reduce amount of carachters to be returned, I'am able to recieve data.

select SUBSTR(t_name, 1, 15) as T_NAME
from MY_TABLE

Also if the amount of charachters in the field is 30, the function "view data" can return the concatenated SUBSTR

select SUBSTR(dja.t_name, 1, 15) || SUBSTR(dja.t_name, 15) as T_NAME
from MY_TABLE

What the problem could start from?

In DS - column is VarChar 512, Nullable = Y

DB it self has the following NLS:
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET AL32UTF8

As stated in the DS manual, I've set the following for the Oracle Connector:
NLS_LANG env variable = AMERICAN_AMERICA.AL32UTF8

Also the NLS_LANG in the OS registry for Oracle client is also:
AMERICAN_AMERICA.AL32UTF8

Windows code page according to the chcp command is 866

Your help is much appreciated!

[chulett]

Did you look up what exactly an "ORA-1406" means? A quick google for that returned lots of hits that explained it and detailed ways people have dealt with it. It has also been discussed here several times.

[Dmitriy]

Hi Chulett!
Ofcourse i did some before posting!

ORA-01406: fetched column value was truncated

My column in DataStage is varchar 512,
DB field value is varchar2 (32 char) and contains 30 cyrillic charachters.

Seems that there is no reason to truncate my 30 characters

NLS values of the DB and NLS_LANG environmental variable for Oracle Connector match with each other:
AMERICAN_AMERICA.AL32UTF8

Also I've found the following conclusion about this error:
"Error:
ORA-01406: fetched column value was truncated
Cause:
In a host language program, a FETCH was forced to truncate a column value.
Action:
The options to resolve this Oracle error are:
Try modifying the program to store a larger buffer value.
"

If there is a way to modify (increase) a buffer value in some Oracle Connector settings?

[ray.wurlod]

Try using VarChar(96) in your job design.

[Dmitriy]

Hello Ray!

Why 96? I used it but with no success

[jim.paradies]

The magic number 96 comes from the fact that you are trying to read 32 Cyrillic characters from a database with NLS character set UTF-8. This is a multi-byte character set wherein the Cyrillic characters are encoded using 3 bytes per character.

96 should have worked.

[chulett]

I used it but with no success

Where exactly did you use it?

[Dmitriy]

Hi Chulett and Jim! Thanks for the info.

I used it but with no success

Where exactly did you use it?

I've set it inside the Oracle Connector Stage, on the "Columns" tab:
SQL Type = Varchar
Length = 96

Probably i've missed the point, may be I should set it in some other place to be able to read my 32 multi-byte characters?

Thanks for the help!

[Dmitriy]

Also what I've found concerning the ORA-1406 :
There was an APAR

JR36822: IS81 SERVER OCI JOBS FAIL WITH 'OCI FETCHED TRUNCATED DATA' WHEN NON 7-BIT CHARACTERS ARE READ FROM ORACLE (UTF8)
https://www-304.ibm.com/support/docview ... wg1JR36822

for DS 8.1 on the IBM site.

It tells that there was a problem caused by an incorrect buffer size within the D/S OCI plugin. DataStage Server stores data as UTF-8 on NLS enabled D/S systems and the column size is defined in characters, and
additionally, the D/S Server does not impose column sizes during
normal processing. The OCI plugin is taking the column size as
bytes rather than characters when defining its buffer(s).

This APAR is in the DS 8.5 Fix Pack1 fix list.
We thought maybe it also can solve our issue with the Oracle Connector Stage and have applied the DS 8.5 Fix Pack1 on all tires, tested the Oracle Connector stage but again with no success.

May be somebody successfully solved the problem of reading multibyte characters with Oracle Connector Stage?

[chulett]

Well, it did say it was for the OCI stage, not the Connector. It's been a month - well past time to involve your official support provider, me thinks.

[Dmitriy]

Hi!
I're right Chulett :D

We could aford such a time lag because we were in the Job design up to our ears
Now it's time to test and such a surprise.. )
So, if we well get the solution I will post here or maybe somebody else will post if he knows.

[Dmitriy]

Regarding the ORA-1406 problem in Oracle Connector Stage with multi-byte characters - with the help of IBM guys, we got the following result:

using NVarChar SQL type (instead VarChar with Unicode attribute) allows to run the complete Job containing Oracle Connector without ORA-1406 error.
But while trying to use "View data" in the stage itself still results in ORA-1406.

So problem can be treated as solved.

[Slayer14]

Hello,
Is there any other updates to this discussion?? Switching to NVARCHAR does not work, nor does making the varchar(96). Its marked as resolved, but I am not sure it should be.