DSXchange

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.

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.

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.

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.

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

Where can you set the preserve blanks option.

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

APT_ORACLE_PRESERVE_BLANKS. Its under the Operator Specific environmental variables.

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

Thanks again.

Time to mark your post as "Resolved".

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

Welcome aboard.

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