Oracle Stage rejects records

mgkrishna · Post by **mgkrishna** » Wed Dec 10, 2008 5:17 pm

Hi,
We are trying to pull data from DB2 and writing to Oracle table. This uses Oracle stage with "insert/update" processing. We've partitioned the stage using hash with a key that exactly matches our database table.

However, during load process, it rejected a few rows (out of many) with error code of "ORA-1403" (This error means "No data found').

We took all those rejected records and tried inserting them using plain "INSERT INTO " statement in sql*plus and this load was successful.

So, there is nothing wrong with the data itself. Also, all columns are defined as nullable in our table.

We are not able to find out why those records were rejected. Can someone please give us a clue on what could be the problem, OR the best way to identify the issue?

Thank you!

say2prabhu1 · Post by **say2prabhu1** » Wed Dec 10, 2008 7:05 pm

Just check with single records alone ... If it suceed ,check with 10 records..
There may be possibility of constraint violation ...

Put a reject file from the DB in datastage and capture ...

Check all the columns thoroughly..

chulett · Post by **chulett** » Wed Dec 10, 2008 7:27 pm

Any chance you have a trigger on your target table?

mgkrishna · Post by **mgkrishna** » Thu Dec 11, 2008 9:01 am

Hi,
thanks for all your suggestions.
There are no triggers on this table.

Also, there is index, but since we use insert/update logic, how could there be a constraint violation which would result in ORA-1403 (no data found)?

At least the error should have been "primary key violation" or "cannot insert nulls" etc...right?

I appreciate you all taking time to respond to this.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Dec 11, 2008 9:03 am

1403 (No Data Found) is from some select. So it has nothing to do with Insert/Update - unless it impacts by triggering a select.

mgkrishna · Post by **mgkrishna** » Thu Dec 11, 2008 10:49 am

Hi,
thanks for all your suggestions.
There are no triggers on this table.

Also, there is index, but since we use insert/update logic, how could there be a constraint violation which would result in ORA-1403 (no data found)?

At least the error should have been "primary key violation" or "cannot insert nulls" etc...right?

I appreciate you all taking time to respond to this.

mgkrishna · Post by **mgkrishna** » Thu Dec 11, 2008 10:51 am

Hi Sainath,
Yes. That is what I'm surprised. 1403 should be from select, but we are getting it in the Oracle stage that only has insert/update.
We are capturing SQLERROR in the reject file and strangely it throws 1403 which should never come for either insert or update.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Dec 11, 2008 11:14 am

Are you using Stored Procedure to load the target?

3 things you can do....
1.) Create a constraint just before the target (Oracle) stage so that no rows are written into it. Try running the job.
2.) Check the table(s) dependant on the target table. If you do not have the permission, your DBA must be able to help.
3.) Run a rdbms trace on the user you are using to load in the target. That will show the reason.