ORA-01400: cannot insert NULL

anusha · Post by **anusha** » Wed Jul 07, 2010 8:09 am

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.

sample data:

Code: Select all

The value of the row is: SEC_NAME = Singa [b]SEC_ID = TMA00000030-02 [/b]Location_ID = PLT00002597 STATUS_DATE = 2010-06-24 05:28:12 SEC_DECALRATION = Accepted REASON = N/A Holder_ID = N/A HOLDING_TYPE = N/A SEC_TYP = N/A

Marked in bold is the entity(SEC_ID), which has got the value but error pops up with this unbelievable error message causing the jobs failure.

Could you people help me with this.

ArndW · Post by **ArndW** » Wed Jul 07, 2010 8:15 am

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)

anusha · Post by **anusha** » Wed Jul 07, 2010 8:38 am

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:

Code: Select all

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jul 07, 2010 9:00 am

Is that the row that is causing the error ?

Add a reject stream and include a peek stage.

Check whether SEC_ID is number.

Also check whether there is any trigger enabled for that table.

Try inserting the row manually from SQLPlus or TOAD.

austin_316 · Post by **austin_316** » Wed Jul 07, 2010 9:03 am

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

anusha · Post by **anusha** » Wed Jul 07, 2010 9:09 am

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.

So suggest me, anything still I am missing.... :D

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jul 07, 2010 9:20 am

Create a temporary table with identical layout (without any keys) and load the data into the new table.

laknar · Post by **laknar** » Wed Jul 07, 2010 11:01 am

Double check the mapping.
Do you have any Unique Indexes or Triggers on Table.

nagarjuna · Post by **nagarjuna** » Wed Jul 07, 2010 12:21 pm

Hi Anusha ,

Do you have spaces in that column ? May be someone might have changed APT_ORACLE_PRESERVE_BLANKS variable value .

nagarjuna · Post by **nagarjuna** » Wed Jul 07, 2010 12:24 pm

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 .

DSRajesh · Post by **DSRajesh** » Wed Sep 01, 2010 8:44 am

Hi

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.

Please help us here to resolve the strange issue.

Regards
RD

DSRajesh · Post by **DSRajesh** » Wed Sep 01, 2010 10:23 am

Hi All,

Please suggest me any thing here to resolve this strange issue where the same one executing fine in dev and test environments.

Only production giving this strange error.

ray.wurlod · Post by **ray.wurlod** » Wed Sep 01, 2010 4:30 pm

What's different about the production environment? Clearly "nothing" is not the correct answer.

DSRajesh · Post by **DSRajesh** » Wed Sep 01, 2010 11:20 pm

Hi Ray,

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.

ray.wurlod · Post by **ray.wurlod** » Thu Sep 02, 2010 12:34 am

Something is clearly different. Find out what it is. It might be in the operating system, the database(s), the authentications, anything.