Space Issue

santoshkumar · Post by **santoshkumar** » Thu Jan 05, 2006 4:55 am

Hi,

I am inserting space into the target field (oracle table), something like this
if isnull(source field) then space(1) else source field

But somehow it seems null is being inserted instead of space.

Any thoughts on this.
Thanks in advance.

regs
santosh.

kumar_s · Post by **kumar_s** » Thu Jan 05, 2006 5:27 am

HI,

You can try giving the condition as

Code: Select all

If isnull(source field) or "" then space(1)

else source field.

-Kumar

ArndW · Post by **ArndW** » Thu Jan 05, 2006 5:30 am

Please write your output to a sequential file to test this - if there are spaces in the text file then your problem lies in the Database portion. If your target database is Oracle and the datatype in the database is VarChar2 then Oracle will put in a null. There are also PX load options which might affect this, notably APT_ORACLE_PRESERVE_BLANKS

santoshkumar · Post by **santoshkumar** » Thu Jan 05, 2006 9:56 am

Hi,

Thanks for reply, When I load the data in flat file or a dataset then I am able to see a space inserted. But when I load into Oracle table, Then its inserting as Null.
Where can I see this APT_ORACLE_PRESERVE_BLANKS and what setting I need to do to rectify this issue.

Thanks in advance.

Regs
Santosh

ArndW · Post by **ArndW** » Thu Jan 05, 2006 10:05 am

Santosh,

please read the Parallel Job Advanced Programmer's Guide, page 6-25 for the specific information for the APT_ORACLE_PRESERVE_BLANKS information; it is very easy to include in your job parameters and of course you want to force it to preserve blanks so the setting would be "TRUE". In addition you will need to make sure that you are not using VarChar2 datatypes for strings that will contain only one or more blanks in Oracle.

santoshkumar · Post by **santoshkumar** » Thu Jan 05, 2006 10:40 am

Hi ArndW,

Thanks again. I tried the option of APT_ORACLE_PRESERVE_BLANKS in job parameter with setting True. But even then the issue persists.
My source is Oacle and Target is again Oracle.
In the source I am using user defined Query in follwing way.
NVL(Source Field,' ') as Source Field and Then its a plane mapping to Target field. But instead of a single space being populated Null is getting populated, And even the target field is Char (1)

Any Thoughts.

Thanks & Regards
Santosh

ArndW wrote:Santosh,

please read the Parallel Job Advanced Programmer's Guide, page 6-25 for the specific information for the APT_ORACLE_PRESERVE_BLANKS information; it is very easy to include in your job parameters and of course you want to force it to preserve blanks so the setting would be "TRUE". In addition you will need to make sure that you are not using VarChar2 datatypes for strings that will contain only one or more blanks in Oracle.

ArndW · Post by **ArndW** » Thu Jan 05, 2006 10:49 am

Try a very simple job, create a dummy source file with 10 records; in your transform stage put a constant derivation of " " for an output data type of CHAR(1) that goes to your table. I prefer to use a row generator stage, but stick with the source sequential file if you haven't done this before.

What gets put into Oracle with a the APT_ORACLE_PRESERVE_BLANKS set to TRUE? a null or a space?

santoshkumar · Post by **santoshkumar** » Thu Jan 05, 2006 11:45 am

Hi ArndW,

When I used the Environment variable "APT_ORACLE_PRESERVE_BLANKS " in job parameter with Setting as True. Even then instead of a space being populated , Null is getting populated.
I am really not sure why this is happening.

To over come this problem, I tried using Close Command option in Target Oracle Stage where I wrote a command something like this
UPDATE table_name SET field_name =' ' WHERE field_name IS NULL

With this my issue is resolved but I thought I should'nt have come to this point. My question is Why is apt_oracle_preserve_blanks not working.

Thanks & Regards
Santosh

OddJob · Post by **OddJob** » Fri Apr 20, 2007 10:03 am

I am having a similar problem where if I have a char type record containing a full fields worth of spaces it is getting converted to NULL.

I note from the Oracle loader logs that my non nullable fields (!) have the following null processing...

NULL if <field> = BLANKS

Beats me why this should be here for a field I've said can't be NULL

I've tried using $APT_ORACLE_PRESERVE_BLANKS but it makes no difference. This variable doesn't exist in the Operator Specific section of the Admin Env Var dialog, I have to add it myself as a User Defined one. Also, my documentation makes no reference to it, contrary to what I've seen on dsxchange.

Do you think there is a version specific problem here - for info I'm running 7.5.0.1

ivannavi · Post by **ivannavi** » Fri Apr 20, 2007 5:19 pm

Look at this to prove you're not crazy:
viewtopic.php?t=108979&highlight=

And try APT_ORACLE_LOAD_DELIMITED. It involves delimiters which you can change and, unfortunately, double quotes which you cannot change.
Therefore, if you have double quotes in your data, you're out of luck.

ivannavi · Post by **ivannavi** » Fri Apr 20, 2007 5:29 pm

Forgot something:
It also makes difference if your target field is char or varchar2.
It even makes difference if "not null" constraints are enabled or disabled while loading.
This is what I remember from fiddling with this, but in the end I thought it's easier to loose the spaces and make the field in the table accept nulls.

OddJob · Post by **OddJob** » Mon Apr 23, 2007 10:15 am

So, are you saying APT_ORACLE_PRESERVE_BLANKS doesn't work if it's not available in Admin? My view is it makes no difference whether I set this environment variable or unset using $UNSET.

Furthermore, this NULL handling in the ctl file is at odds with the nullability of the field i.e. why potentially set a field to NULL when the field can't handle NULL?

BTW I saw your post before, but I'm still pulling my hair out

ivannavi · Post by **ivannavi** » Tue Apr 24, 2007 10:05 am

So, are you saying APT_ORACLE_PRESERVE_BLANKS doesn't work if it's not available in Admin?

No, I hope It does something when some requirements are met. I just don't know which requirements.[/quote]

OddJob · Post by **OddJob** » Tue Apr 24, 2007 10:24 am

I think I sorted the problem and posted an update as follows:

viewtopic.php?t=109371