Possible truncation of variable length string

[throbinson]

1. My Source Oracle Enterprise Stage has the field defined as VARCHAR2(20).
2. A describe in both Toad and SQLPLUS reveal the field as a varchar2(20).
3. I imported the table definition as a Orchestrate schema also varchar(20)
4. The two dbs have different NLS_CHARACTERSET
5. I run the job and get this warning;

link: When checking operator: When binding output interface field "DBUSER" to field "DBUSER": Implicit conversion from source type "string[max=60]" to result type "string[max=20]": Possible truncation of variable length string. [api/interface_rep.C:6243]



It's a third party Oracle db out of my control so I cannot change it. Other apps use the other Oracle database so this too will not change.

Why the Runtime Metadata mis-match and what can be done about it?

[chulett]

Multi-byte character set, I'd wager. What two NLS_CHARACTERSET settings are involved here?

[throbinson]

I'll find out. Why does Toad, SQLPLus and the Orchestrate import correctly interprete it while the DS runtime doesn't?

[chulett]

We have yet to determine it is being incorrectly interpreted.

Not sure that's a real "apples to apples" comparison. But then I'm no NLS or PX expert, others would need to chime in based on the results of your investigation.

[throbinson]

I have isolated the SQL that appears to be causing the problem.

When I include a Sequence call for the next value (SEQ.nextval) in the User defined SQL I get the warnings. When I default the Seq target field to 1 (removing the Sequence call), I do not get the warnings.

So to recap:
User defined SQL selecting from a synonym defined as a dblink that contains a call to a Sequence, is throwing warnings, the same SQL without the sequence call throws NO warnings.

To answer Chulett
1. An import table definition correctly defines the fiels.
2. A desc in TOAD and SQLPlus correctly defines the fields.
3. Running the DS job incorrectly defines the fields.

Not sure why I can't say DS is incorrectly interpreting the runtime metadata. How can it do it correctly at design time and in different toolsets and yet differently at DS runtime.

[chulett]

What two NLS_CHARACTERSET settings are involved here?

Did you ever get the answer to this question? Seems like it might be a piece of information needed to get you a proper answer. From someone else.

[throbinson]

NLS_CHARACTERSET
Physical db - US7ASCII
dblink db - AL32UTF8

[throbinson]

Define a Oracle Enterprise Stage Option in the
Additional Connection Option Property
Of db_cs=US7ASCII.

This eliminates the conversions and the warnings on Metadata mis-matches.

[chulett]

Good to know! Thanks for posting that.

[throbinson]

I was wrong!
Defining a db_cs=US7ASCII
Additional Connection Option property had no affect.
A case has been opened with IBM.

[oracledba]

This mismatch is happening because your source oracle database has the field DBUSER defined as VARCHAR2(60) and your source oracle stage has it defined as VARCHAR2(20). WHatever the reason may be there is a simple fix for this. Just cast it in as varchar 20 in your oracle query in source stage and this warning wont show up.



For Ex:

I am assuming your table name is DLZ_TABLE. You can substitute your table name.


Select CAST (DBUSER as varchar(20)) DBUSER
FROM DLZ_TABLE


In source oracle stage meta data defintions

Column SQL Type Len Nullability
DBUSER VARCHAR 20 YES




WHENEVER YOU GET THIS PROBLEM WITH STRING TRUNCATION YOU CAN USE THE CAST FUCTION AS SHOWN ABOVE.