Update several rows, one failes - which one?

[ija]

Hi.

Is there a way, how to identify, which row failed, if I try to update several?

The background: we have a conversion tool with high requirements on performance. We convert entities record by record, because some may fail and we need to flag them. We use a cursor, which works fine, but is slow. If we would convert by hunders of rows, we would flag all of them when only one fails.

 

[Bimalaggarwal]

If you are using a cursor, then this should be no problem. Just flag the current record by rowid.
I think this should be easy.
You may explore "Select .. for update" statement also.

 

[ija]

The problem is, that cursor is too slow for our purpose. We would like to replace it by updates of multiple rows.

 

[sem]

If your statement fails due to some trigger you may insert the row info into some table using autonomous transaction.

 

[Guest_imported]

A trick I like to use is to execute
sys.dbms_system.set_ev for the sid and serial# of
interest, the event code 10046, level 12, with
a null action. The 12 is a bitmap that specifies
that our trace file should include wait events,
and bind variables and the standard sql_trace
functionality.
Your session id can be found a number of ways.
v$session should give you the serial number.
Searching for the ERROR you'll find the insert cursor
and sequentially the most recent bind.
In the case of integrity constraint failure you'll
see the appropriate object information to find the
problem using the dba tables, but you can certainly
find the sequentially most recent bind if the row
itself if of interest.

This is a suggestion that is geared toward someone
actively debugging and tuning, rather than a standard
production environment though.
The trace file itself will be found in a directory
you can see from v$parameter, and will be named for
the oracle process number.

 

[ija]

Sem, johnfuller360, thanks a lot. This is still not what we need. We do not use any triggers, because they are slow, and we do not need this for debugging purposes, but for production conversion.

 

[sem]

Why does your statement fail? Constraint? Try to disable it, make inserts and then enable it (deleting bad records using exception table). You may also speed up your cursor procedure using bulk binds. Thus only one batch will fail.

 

[angiole]

I would understand an UPDATE to fail because of constraints as mentioned earlier, but also because of invalid data type or value too large for cloumn-width.
If you are using a conversion tool, it may allow you to define output types and lengths outputing only the good records, and placing bad records in a separate file for follow-up.

 

[ija]

The statement fails because of NOT NULL constraint.
The company is replacing one system by another and unfortunatelly it is the master system for our system. So we need to change IDs in about a half of database.
We first search for previous ID for each entity, which is stored in the NOT NULL column. In very few (quite obscure) cases happens, that no matching record can be found and then we get the error.
It happens so rarely, that we do not want to perform a special check.

Sem - good idea, but this is a multi-process application. Data from the one process are quickly taken by another processes... There is no point, when I could enable the constraint.
If I would disable it completely, I would get referential problems in the database (the system does not use foreign keys and we are not using standard interfaces and conversion tools).

Does "bulk bind" mean inserting/updating several records in one statement? In that case this is what we try to achieve. But then I need to process the failed records somehow. And I would like to have as many records in one batch as the rollback segment allows (starting with 1000 but probably about 50000).

Very sad about the results so far... Help! :-( :-( :-(

 

[sem]

Yes, bulk binds allow to select/insert records using a collection as a buffer. The link is to 9i documentation, but it will work (maybe with minor details) on 8i too.

http://otn.oracle.com/docs/products.../901_doc/appdev.901/a89856/05_colls.htm#23723

You have to process all records, but it will be much simplier to find erroneous records (scanning already selected rows in memory).

Though I quite sure that checking conditions by transforming tool is much pure way.