ORA-01400: cannot insert NULL into

[ravij]

Hi,

We have migrated the project from DS V6.0 to V7.5.1a server edition.

when testing the jobs in new version i got one issue. When I ran the job I am getting the warning message like:

STGDiscountsODSLoadActTypeUANZ..Transform: ORA-01400: cannot insert NULL into ("ODS"."ACTIVITY_TYPE"."ACTIVITY_TYPE")

and

STGDiscountsODSLoadActTypeUANZ..Transform: The value of the row is: ACTIVITY_TYPE_ID = 29583 ACTIVITY_TYPE = CONTRACT ACTIVITY_TYPE_DESC = NULL ACTIVITY_SUBTYPE = DISCOUNTS DATE_CREATED = NULL CREATED_BY = STGDiscountsODSLoadActTypeUANZ LAST_UPDATE_DATE = NULL LAST_UPDATE_BY = STGDiscountsODSLoadActTypeUANZ

Here ACTIVITY_TYPE_ID is the key column and ACTIVITY_TYPE has Not Null constraint. Those columns do not have null values but its giving the error like this..
In the target table I used userdefined sql. i.e.

INSERT INTO ACTIVITY_TYPE (ACTIVITY_TYPE_ID,ACTIVITY_TYPE,ACTIVITY_TYPE_DESC,ACTIVITY_SUBTYPE,DATE_CREATED,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_BY)
select :1,:2,:3,:4,:5,:6,:7,:8 from dual
where not exists (select 1 from activity_type
where activity_type_id = :1)

The data type for ACTIVITY_TYPE is Varchar in source and Char in Target with length 10.
What could be error .. I don't nderstand ..

Please help in solving this issue.
Thanks in advance...

[ravij]

Hi,

I would like to add one more point here. The same job when I ran in the older version i.e. V6.0, its running fine without any warnings. Only the problem is in new version V7.5.1a

[chulett]

Change the Array Size to 1 temporarily and see what record is logged as the problem child. Array Sizes over 1 can confuse the logging process.

[ravij]

Thanks Craig,

I could find the where the problem is by setting the Array size to 1.

There is a NULL value trying to load into the Not Null column.

Thanks alot..

[chulett]

Well, there you go! Please mark the topic as Resolved using the button on the top of the page.