NVARCHAR issue while updating Data in Oracle

[Raamc]

Hi All,

I have a job which update data in a table in Oracle.
While importing the metadata from datastage by using ODBC meta data connectivity, Datastage converted all VARCHAR datatypes into NVARCHAR type. I am using the same meta data while updating the same table after few joins in my job.

This job went fine when I was running it on variuos environments, now we imported the same job into other environment and trying to run.

It is throwing the following warning at each column and after few warnings job is getting aborted.

SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated

We are not geting the maximum length data, all the data is less than the specified length for each column in ODBC, then we can't understand what it is trying to truncate?

If we change datatype from NVARCHAR to VARCHAR it is running and updating the data as expected, but we can not do that change since we have used same NVARCHAR in around 20 jobs.

Can any one suggest me how to solve this issue?

[ray.wurlod]

Depending on the database and how it has been configured, lengths for string data types may be reported in bytes rather than in characters. You get fewer Unicode characters than you have bytes.

What lengths are reported when you import the table definition? Use these.

[Raamc]

Length's are same for all the columns Ray. Only VARCHAR to NVARCHAR and CHAR type to NCHAR are converted while importing the metadata.

And regarding the UNICODE, can you please explain a bit more?

[ray.wurlod]

A column may be declared, for example, as VARCHAR2(20 BYTES) or VARCHAR2(20 CHARACTERS). The latter takes up 60 bytes in Oracle. You can get DataStage to manage it properly if declared as VarChar(60).

There is a setting in Oracle to switch the default from BYTES to CHARACTERS or vice versa, but I can't recall what it is. I *think* it's been described on DSXchange previously.

[chulett]

Done via length semantics but not something I find DBA peoples are willing to change. Typically.