Oracle Enterprise Stage: Converting Blanks to Null

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
dsusr
Premium Member
Premium Member
Posts: 104
Joined: Sat Sep 03, 2005 11:30 pm

Oracle Enterprise Stage: Converting Blanks to Null

Post by dsusr »

Hi All,

We are facing an issue while using Load-Append in Oracle Enterprise Stage where all the Blanks are getting converted to Null.

We tried setting the environment variable $APT_ORACLE_PRESERVE_BLANKS to true at job level but still the control file for SQL Loader have the condition: 'NULL if startlength:endlength = BLANKS'.

I have gone through few of the posts which says the variable has worked but just wondering if anyone has tried this on Sun Solaris Install.

Regards,
dsusr
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Check $APT_STRING_PADCHAR as well.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
dsusr
Premium Member
Premium Member
Posts: 104
Joined: Sat Sep 03, 2005 11:30 pm

Post by dsusr »

ray.wurlod wrote:Check $APT_STRING_PADCHAR as well. ...
Hi Ray,

Thanks for the reply but not sure how this variable is going to have an impact on this issue since we are only dealing with Varchar fields. Anyway the value is set to Null for APT_STRING_PADCHAR i.e. 0x0

Our problem is that we have a Varchar2(30) field which is having a single space character in source. While loading the data using Load-Append Method the Single Space is getting converted to Null.

The possible workaround is to use Upsert with Insert Only condition.

Regards,
dsusr
Kryt0n
Participant
Posts: 584
Joined: Wed Jun 22, 2005 7:28 pm

Post by Kryt0n »

dsusr wrote: The possible workaround is to use Upsert with Insert Only condition.
What "insert only" condition?

Have you tried sending the output to a flat file instead of a load to Oracle to confirm the space does exist and not stripped somewhere during processing?

Second, you sure it is a varchar field? If so, do you have "NULL if x:y = BLANKS" on all varchar fields?
Post Reply