null handling

[rafidwh]

Hi All,
my source is a flat file and target is oracle(oci).
I need to update the table based on the key columns, but in my situation if the source has spaces in the not null columns it is taking as null values and is rejected in the table.

Do I need to populate those columns with spaces by handlling null or is there any other options.

If my column length is 5 should I need to populate that column with 5 spaces.
I am using trim function for all the columns

Suggestions required please

Thanks in advance
Saik

[kumar_s]

It is again highly depends on the datatype of that column. And more over, what is the output log given by datastage for the row reject.

[prabu]

Hi All,
my source is a flat file and target is oracle(oci).
I need to update the table based on the key columns, but in my situation if the source has spaces in the not null columns
I am using trim function for all the columns
Suggestions required please
Thanks in advance
Saik

Hi Saik,
trim of all space value is null, right. You can change the column defintion of the columns you want to .[allow spaces to not null

[kumar_s]

For the data type of varchar it is ok to give single space, in case of char datatype it is must to have 5 spaces. If it is decimal , it would be considired as null. There may be some other reasons, like unique constrains voilation for record reject.

[rafidwh]

Kumar,

My datatype for source col is varchar and the corresponding datatype in target is char.

The error in the log is ora-01400
cannot insert null into not null columns

Saik.

[kumar_s]

May I know the purpose of using trim for all the column.
Yes, you try the option suggested by prabu. By using trim, you are removing the left out space, where the target expects at least 5 space in the char. If trim is part of your logic, you can use handle_null.

[kris007]

Hi All,
my source is a flat file and target is oracle(oci).
I need to update the table based on the key columns, but in my situation if the source has spaces in the not null columns it is taking as null values and is rejected in the table.

Do I need to populate those columns with spaces by handlling null or is there any other options.

If my column length is 5 should I need to populate that column with 5 spaces.
I am using trim function for all the columns

Suggestions required please

Thanks in advance
Saik

Set Null Field Value property to '' (notice no space in b/w quotes).
and add the following environment variable
APT_IMPEXP_ALLOW_ZERO_LENGTH_FIXED_NULL set it to 1.
You should be good to go. You dont have to check for spaces again. But if you are performing any transformations on these fields make sure you perform ISNULL check before.

[vinaymanchinila]

Hi,
I am trying to load an oracle table and have issues loading null values into NON-Nullable fields.

I did use in the transformer preceding the oracle stage

1) If IsNull(Src.Col) then " " else Src.col
(Also tried with If IsNull(Src.Col) then ' ' else Src.col)

2) Set APT_ORACLE_PRESERVE_BLANKS to TRUE

But it still rejects with sql code 1400.

When I write the same stream to a flat file it shows space in the field values.

The target contains Char, Decimal, VarChar and integer columns, is there any other parameter that we need to set before trying to load space into Oracle non-nullable fields?

Thanks,

[thumsup9]

Try this for char and varchar fields

If IsNull(Src.Col) then '' else Src.col Convert(char(000)," ",Src.Col))

[vinaymanchinila]

hi,
I didnot get your else part and also there is a extra ')' at the end.

I am trying this

If IsNull(Src.Col) then '' else (Convert(char(000)," ",Src.Col))

Will let you know.

[thumsup9]

Yes I meant the same..HTH

[vinaymanchinila]

Hi,
It still rejects with the 1400 code, looking closer it is the fields of char type, I wrote the rejects from the oracle table to a flat file and the columns with char type do not have spaces and are null.

Thanks,

[vinaymanchinila]

Finally i think i have figured out the issue, the char column comming in is not being identified as Null, I used the following

IsNull(Trim(Src.Col))

and I made a simple job that checks and writes the nulls and not nulls based on Src.Col to 2 different streams and all the rows go down the NOT NULLS path !


When I open the file to read the column it is blank .
Any ideas on how to trim non-readable data?

[kool78]

Finally i think i have figured out the issue, the char column comming in is not being identified as Null, I used the following

IsNull(Trim(Src.Col))

and I made a simple job that checks and writes the nulls and not nulls based on Src.Col to 2 different streams and all the rows go down the NOT NULLS path !


When I open the file to read the column it is blank .
Any ideas on how to trim non-readable data?

Can you try

If Trim(Col)="" then "default value" else if IsNull(Col) tehn "default value" else Col...

Let me know if this works out for u...

[ray.wurlod]

Wouldn't it be easier just to set the Null Field Value property in the Sequential File stage, for example to "<NULL>", so that spaces continue to be treated as spaces?