Hello ...
I have DS Job which is failing with error message as 'ORA-01400: cannot insert NULL into' from last 3 days. This Seems to be a strange behaviour to me because the job is already in production from last 1 year and I am getting the above said error even though there are no Null values in the source dataset which I populate through this Job.
Could you please post the whole error message. This type of problem could occur if someone has modified your table DDL and added a new, not nullable, column -- but the error message will state which column is causing the error.
You can also debug this by putting a PEEK stage right before you database write and outputting all columns for every row (this might be a bit much with production-sized data volumes, in which case make a copy of the job and select a subset of your data but include the row that is causing the error)
We went through most of the scenarios over Database related stuff.
There is no new column is added, no datatypes were modified and whole database schema is untouched with modifications.
we are getting the error as:
countsODSLoadActType.xfm: ORA-01400: cannot insert NULL into ("ODS"."ACTIVITY_TYPE"."SEC_ID")
Same Job Export we have imported into another environment and checked, but that is working very well in this environment. now we are short of ideas, I do think.
Any suggestions are appreciated.
If the target in your job is an Oraclestage then you can create a reject link and send the details into the reject file. after u have executed your job and if there are any records that are not getting inserted into the db thru oracle stage then those records will be saved into this reject file along with the sql error code becoz of which this record got rejected.then you can view the data and know where there is null value.
hope it might help you
Yes, I tried this with peek stage and everything and data which hits the database is also very ok.
It's not for a single record instead for all the records. not even single record is getting inserted into database. For every record it's throwing the same error nearly 20,000 in number.
And there are no database level triggers exists on the table but we have some referential integrity built on the table. We Tried loading by disabling the foreign keys on the table even then also I am encountering this error.
I believe that you are using oracle enterprise stage .If you are facing this issue with a column datatype number then there is known bug . Check the same forum you will get to know more details .
I am also getting similar error with same king of scenario.
We have parent child tables with referential integrity between tables and we are disabling the keys before loading enabling after loading using datastage.
While loading we are facing Cannot insert NULL error even value exists in some cases.
I have data for a particular column CategoryID getting from source dataset and while loading into target table it is giving error ora-01400:cannot insert NULL.
Same job is working fine with same value for methodid column in dev and prod environments.
What needs to be done with this to resolve in production environment.
Everything in database and datastage jobs is same in all dev,test,prod environments.