mysterious null characters added in varchar field

[olivier madelin]

hi,

I've got a problem on an ORACLE ORAOCI9 stage:
I'm in a update/insert configuration
I want to update a varchar(5) field declared as VARCHAR2(5) in ORACLE
my field is mapped in my DS job and my ORAOCI9 stage like a varchar with length 5

What happened?
DS changed the length field from 5 to 7 and completed the value with 2 NULL characters, i mean ascii code is char(0)
Of course ORACLE rejected my row with ORA-01401: value too long
following with ORA-00001: violation unique key when trying to do the insert because the update failed.

I remarked that when i changed varchar to char in the DS mapping, it works,
or when i changed update/insert to insert/update, it works.
No problem, No additionnal null characters, my field is updated.

do you have any solution or explaination ?
any parameter to modify?

thanks

[ArndW]

What value do you get if you add a column with "LEN(In.MyLen5VarCharField)" and output that to a text file? I am fairly certain that the length is already 7 before any conversions.

[olivier madelin]

I did test this.
Length of my input value is 5
the value is "92400"

I did another test:
Input value is "9" and output field is POSTAL_CD in ORACLE declared VARCHAR2(5)

Case 1:
Mapping POSTAL_CD in the transformer is varchar and length 1
Value of field POSTAL_CD in ORACLE after the update is "9 "
I looked with TOAD, length(POSTAL_CD) = 3
Ascii transfo gives Char(57) char(00) char(00)

Case 2:
Mapping in the transformer is varchar and length 2
Value of field POSTAL_CD in ORACLE after the update is "9 "
length(POSTAL_CD) = 4
Ascii transfo gives Char(57) char(00) char(00) char(00)

Case 3:
Mapping in the transformer is varchar and length 3
Value of field POSTAL_CD in ORACLE after the update is "9 "
length(POSTAL_CD) = 5
Ascii transfo gives Char(57) char(00) char(00) char(00) char(00)

Case 4:
Mapping in the transformer is varchar and length 4
The job aborts because the value of POSTAL_CD must be "9 " and value is too long (6).

Case 5 :
Mapping in the transformer is char and length 4
Value of field POSTAL_CD in ORACLE after the update is "9"
length(POSTAL_CD) = 1
if i do my mapping with char, it works.

Case 6 :
Mapping in the transformer is varchar and length 4
and if i am in insert/update configuration instead of update/insert
Value of field POSTAL_CD in ORACLE after the update is "9"
length(POSTAL_CD) = 1
it works too.

[chulett]

Ok... what version of DataStage? What Oracle client version?

[olivier madelin]

Datastage Version 7.5
Oracle client Version 9.2

[chulett]

DataStage 7 dot 5 dot nothing? From what I recall, the original 7.5 release was a little... wonky... and it would behoove you to get the 'latest' point release - at least 7.5.1A unless 7.5.2 is available for your platform.

Not saying that is the cause of this rather odd problem, but... ??

[ArndW]

Do you get the same padding when writing to a sequential file instead of to the Oracle stage? If you were using PX I'd say the culprit is APT_STRING_PAD_CHARACTER but you stated server as the job type. Also, if you declare the Oracle column to be VarChar instead of VarChar2 do you get the same error?

[chulett]

Nobody uses VARCHAR in Oracle.

[olivier madelin]

sorry, I specify for DS it is well V7.5.2

[chulett]

Then I'd say it's time to open an official Support case.

[olivier madelin]

I've got no problem when writing to a sequential file.
I've got the same padding when using VARCHAR in Oracle instead of VARCHAR2.

I will open a case at Ascential support, and i will post the solution.

thank you for your help.

[ArndW]

Tough problem! I hope that support comes back quickly with a solution and look forward to hearing the explanation once they arrive at it.

[chulett]

Same here. And in the meantime, perhaps I'll hold off on any upgrade to 7.5.2 I was contemplating...