Inserting into a not null column.

psriva · Post by **psriva** » Fri Mar 30, 2007 10:02 am

Can Datastage insert spaces into a column defined as NOT NULL?
I tried to insert space in a column defined as not null but the job fails.

kcbland · Post by **kcbland** » Fri Mar 30, 2007 10:07 am

What's the target database and loading method? Most bulk loaders default to assuming a single space into a CHAR type target column should be interpreted as a NULL. There's switches you need to set to alter that behavior. More information please.

DSguru2B · Post by **DSguru2B** » Fri Mar 30, 2007 10:27 am

This is not a datastage issue but rather the type of database. I have, in the past, inserted space in not null columns in a SqlServer table.

chulett · Post by **chulett** » Fri Mar 30, 2007 10:31 am

Nah... I'd wager it's a settings issue as Ken notes. You usually have to tell your process to 'PRESERVE_BLANKS' in some fashion otherwise they typically get stripped. Then your space gets turned into an empty string and blows the NOT NULL constraint.

More information please.

psriva · Post by **psriva** » Fri Mar 30, 2007 10:33 am

The target database is Oracle and the write_method=load. The target column is defined as not null.

My input file consists of:

record nullable
{final_delim=none, record_length=fixed, record_delim_string='\n', delim=none, quote=none}
(
code:not_nullable string[1] {width=1};
)

I get an error saying inserting null into a not null column.

Please let me know if you need more info. Thanks

psriva · Post by **psriva** » Fri Mar 30, 2007 10:35 am

Where can you set the preserve blanks option.

chulett · Post by **chulett** » Fri Mar 30, 2007 10:48 am

It's one of the many APT environment variables, from what I recall.

kwwilliams · Post by **kwwilliams** » Fri Mar 30, 2007 12:15 pm

APT_ORACLE_PRESERVE_BLANKS. Its under the Operator Specific environmental variables.

psriva · Post by **psriva** » Fri Mar 30, 2007 12:55 pm

Thank you all of you, very very much. By setting that environment variable to TRUE, my job works fine.

Thanks again.

DSguru2B · Post by **DSguru2B** » Fri Mar 30, 2007 1:12 pm

Time to mark your post as "Resolved".

bonds · Post by **bonds** » Mon Sep 24, 2007 2:25 am

DSguru2B wrote:Time to mark your post as "Resolved".

Hello All,

I have found that using APT_ORACLE_PRESERVE_BLANK = True option also Pads blank spaces behing the passed string.

i.e. eventhough i pass only 5 characters to a field defined as Varchar 30, in database it is actually stored as 30 instead of 5.

Database: Oracle
Wrtite Mode: LOAD -> Replace

Please suggest how to resolve this issue.

Thank You

Satish

ray.wurlod · Post by **ray.wurlod** » Mon Sep 24, 2007 3:42 am

Welcome aboard.

Procedural Note
We do not hijack threads. For a new topic, we start a new thread.
Please do so.

DSXchange

Inserting into a not null column.

Inserting into a not null column.

Size in DB