DSXchange

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

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.

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.

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

Datastage Version 7.5
Oracle client Version 9.2

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... ??

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?

Nobody uses VARCHAR in Oracle.

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

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

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.

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

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