Home » Developer & Programmer » Precompilers, OCI & OCCI » How to detect offending record for an update statement (any)
- How to detect offending record for an update statement [message #335760] Wed, 23 July 2008 11:05 Go to next message
abadulescu
Messages: 4
Registered: July 2008
Location: New York, USA
Junior Member
Hi,

Does anybody know if there is a way to run an update statement through OCI and make it stop on the first offending record and then identify that record?

Here is an example of what I need to do:
I run an update with 5 records in the input buffer like this:
- the 1st record exists in the table
- the 2st record exists in the table
- the 3rd record does not exist in the table
- the 4rd records exists in the table but is violating some unique constraint
- the 5nd record exists in the table

I need to report that the 4th record from the input buffer has a problem, and then commit records 1, 2 and 3. Do not commit record 5.

So far I have tried the following:

- OCIStmtExecute with OCI_DEFAULT. It stops on the first error, but I don't know how to identify the bad record. OCI_ATTR_ROW_COUNT returns the number of affected records in the target table. I didn't find any other attributes that would return 4, for the 4th bad record in the input buffer.

- OCIStmtExecute with OCI_BATCH_ERROR. I can identify the bad records in the input buffer by using OCI_ATTR_DML_ROW_OFFSET. However, OCIStmtExecute does NOT stop on the first violating record. It loads the whole input buffer and then reports which records were in error. I don't know how to commit only up to the first bad record -- the 4th in the example.

Thanks a lot!
Ana-Maria

Report message to a moderator

- Re: How to detect offending record for an update statement [message #335763 is a reply to message #335760] Wed, 23 July 2008 11:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68755
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I don't think it is possible and the main reason is that you can't give an order of update for your rows in the buffer. Oracle chooses the order it wants, the statement is a whole.

What is the actual need you're trying to satisfy? Why do you want to stop at the first error?...

If there is an order relation in the rows you want to update then you don't have one statement but one transaction with as many updates as needed.
If you don't want to send N updates statemet then create a stored procedure and call it instead passing the array or something like that depending on the rest of your program.

(By the way, you can't commit 3 as it does not exist and so also raise an error.)

Regards
Michel

Report message to a moderator

- Re: How to detect offending record for an update statement [message #335782 is a reply to message #335763] Wed, 23 July 2008 13:52 Go to previous messageGo to next message
abadulescu
Messages: 4
Registered: July 2008
Location: New York, USA
Junior Member
OCIStmtExecute with OCI_DEFAULT works as I need for an insert: it stops on the first bad record, and OCI_ATTR_ROW_COUNT gives me the record count in the input buffer. Oracle documents this attribute as: "For INSERT, UPDATE, and DELETE statements, it is the number of rows processed by the most recent statement." So I was hoping that there must be a way to do the same for any statement, including an update (or a merge with just the update clause, same situation).

I think that Oracle processes the records in the order in which they are in the input buffer. If I update the same record twice (I know, doesn't make much sense), then I see the last updated value in the table after the update has finished with the input array. Oracle seems to execute an update for each record, in the order in which they are in the input buffer.

I need to stop on the first bad record so I can take the proper action when the load fails: skip the record or fix it and then restart the load from the record count where it left off on the previous run (plus 1 if I skip the record).

For the 3rd record, one can argue that a non-existent record is not really an error. Even if it is, I would be happy if the OCIStmtExecute would stop and indicate in the error code that the record does not exist. I would simply ignore that error in my code and proceed to the next record.

Could you provide more insight into how I could solve my problem with a stored procedure as you suggested? I haven't used stored procedures before. What would it do, and how would I pass the data from my input buffer?

Thanks,
Ana-Maria

Report message to a moderator

- Re: How to detect offending record for an update statement [message #335783 is a reply to message #335782] Wed, 23 July 2008 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68755
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I need to stop on the first bad record so I can take the proper action when the load fails: skip the record or fix it and then restart the load from the record count where it left off on the previous run (plus 1 if I skip the record).

Why not load all the records and getting the bad ones, then fix and reload or skip them?

Regards
Michel

Report message to a moderator

- Re: How to detect offending record for an update statement [message #335784 is a reply to message #335783] Wed, 23 July 2008 14:25 Go to previous messageGo to next message
abadulescu
Messages: 4
Registered: July 2008
Location: New York, USA
Junior Member
With my application I can only skip from the beginning of the input. So if record 1, 4 and 7 are bad, I can only skip the first one, not the 4th one, in the middle of the buffer.

Report message to a moderator

- Re: How to detect offending record for an update statement [message #335791 is a reply to message #335784] Wed, 23 July 2008 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68755
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
My question is "do you functionnaly need this?". I'm not talking about implementation.
Or if you prefer, if you have a solution to load all what is correct and give you the bad records, does this satisfy the business need? If not, why? Why do you need to fix one record before going on?
If this is absolutly the case, if the order is important, then you have to make one update after the other.

Regards
Michel

Report message to a moderator

- Re: How to detect offending record for an update statement [message #335792 is a reply to message #335760] Wed, 23 July 2008 15:24 Go to previous message
abadulescu
Messages: 4
Registered: July 2008
Location: New York, USA
Junior Member
Yes, I there is a functional need for this: stop on the the first error and commit everything before that.

My current work-around is first try to update the whole buffer. In case of failure, I rollback and then do the update one by one until I hit the bad record. This works as long as the data between 2 successive commits fits into one buffer. Once I use multiple buffers, I can no longer use this approach. I would have to always update one by one.

Thanks for your help!
Ana-Maria

Report message to a moderator

Previous Topic: Increasing the performance of Pro*c Code
Next Topic: tool to design Flowchart for the given code automatically
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 04 20:59:54 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.