Issue while inserting data in Oracle table using OCI

[parag.s.27]

I having a weird problem here,

While inserting the data in an Oracle table, the control characters are getting attached with the unique key colum which is having a data type of Char(14).

I checked in DS job, instead of inserting it to a table, I populated the data in a File and opened the file in Textpad with visible spaces property set. I could not find any special character. Also I checked the data in table fith HEX decoder, and again I did not find any control character. But when I applied regular expression to detect any control character and bingo, here I found the culprit.

So the summary is, while inserting the data in Oracle table, DataStage is appending the control characters. So is it any setting or any environment variable for Char() data types that can cause it.

[chulett]

"control characters"? Can you post some examples? First suspect would be either your source data or how it is being read.

[vivekgadwal]

I having a weird problem here,

While inserting the data in an Oracle table, the control characters are getting attached with the unique key colum which is having a data type of Char(14).

I checked in DS job, instead of inserting it to a table, I populated the data in a File and opened the file in Textpad with visible spaces property set. I could not find any special character. Also I checked the data in table fith HEX decoder, and again I did not find any control character. But when I applied regular expression to detect any control character and bingo, here I found the culprit.

So the summary is, while inserting the data in Oracle table, DataStage is appending the control characters. So is it any setting or any environment variable for Char() data types that can cause it.

Did you try checking the "Unicode" option for Character / Varchar data?

[Abhijeet1980]

DataStage, by itself wont append any Control characters to the data stream.

The issue could be with NLS/Character set at the Oracle end. Get in touch with Oracle DBA and the NLS used by DataStage.

Many regards,
Abhijit Gaikwad

[ray.wurlod]

If the data type is Char(14) and fewer than 14 characters are provided, DataStage will pad the field with the character specified in APT_STRING_PADCHAR, by default \0 characters, which parag.s.27 may be referring to as "control characters" since they are in Control Set 0.

[parag.s.27]

Ohh Man,

Lots of great suggestions here.

1. Well, the field is a char with unicode extended while extracting. after which I make it only char in the transformer. I am not able to remove unicode in extraction OCI stage as it throws warning in DS8.1. But I suspect that this is not the problem.

2. I can not specifically tell what is this char. let me put it this way. The trim function does not remove it. In table the field has no spaces or special value visible at the end of the field contents. For e.g in TOAD if you double click on a field and then select HEX option then you can see these type of values. But this is also not the case. So none of my testing queries work with simple trim function.

3. The queries only work when during a join operation, I apply Regexp_replace(<col name>,'[[:cntrl:]]','') function. So it is now confirmed that its definitely a cntrl char.

4. I'll try on the environment variable part.

[vivekgadwal]

1. Well, the field is a char with unicode extended while extracting. after which I make it only char in the transformer.

How did you change this to just char? My experience is, if you are extracting Unicode data, we should propagate that. Converting it to Char/Varchar doesn't eliminate the possibility that there might be those characters (from APT_PADSTRING_CHAR) being inserted as Ray pointed out. So, unless you are absolutely certain that the fields with Char data meet the length specified, it is good to propagate unicode all throughout.

However, there might be some other interesting suggestions that I can pick up too. Please post your results from your trials.