Possible truncation of variable length string
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
Possible truncation of variable length string
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?
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?
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
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.
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.
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
Re: Possible truncation of variable length string
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.
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.