Update Select Statement Error - Catch rowid..

Hello All,

I have an update select statement which executes inside a pl/sql block and uses a cursor/fetch to update 1 row at a time and skip rows which result in exceptions.

Running the sql in sql*plus takes around 30 secs per 15,000 rows, but considerably longer when using the cursor/fetch.

I need the exception handling as the pl/sql proc is used in a nightly batch-update via OO4O.

I am trying to find of way to use just straight sql, but with some exception-handling - running the straight sql, capturing the rowid of a failed rec, do not include this row for update, then run again - continue until no exceptions.

e.g.

Proc sql_proc IS

[Update Statement]

End sql_proc;

Function run_sql RETURN BOOLEAN IS

[Call sql_proc]
   

   Exception
[Get rowid of failed record - remove row from update - try sql
again]

Pointers appreciated Received on Fri Aug 16 2002 - 21:50:54 CDT