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!
Oracle Stage rejects records
Moderators: chulett, rschirm, roy
Oracle Stage rejects records
Regards,
- MGK
- MGK
-
- Participant
- Posts: 27
- Joined: Mon Jul 09, 2007 1:06 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.
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.
Regards,
- MGK
- MGK
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
Regards,
- MGK
- MGK
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.