Issues with updating many rows oracle
Having a WHERE clause will only filter which records get updated and nothing else.In addition, your where clause will either always return true or always return false.The number of rows affected by SQL Update can be returned using SQL%ROWCOUNT (For ORACLE) or @@ROWCOUNT(FOR SQL SERVER) Note: In order to return the number of rows updated, deleted, etc..we have to use OUT Parameter in Stored Procedure which will store the number of rows updated,deleted etc..I would like to be able to log it so that I can then go in and modify/correct the specific data value causing the error.declare 2 e_forall_error exception; 3 pragma exception_init(e_forall_error,-24381) 4 ; 5 type t_my_columns is table of my_table.my_column%type; 6 a_my_columns t_my_columns := t_my_columns() 7 ; 8 begin 9 select my_column 10 bulk collect into a_my_columns 11 from my_table 12 ; 13 forall i in 1..a_my_columns.count save exceptions 14 update my_table 15 set my_column = do_something(a_my_columns(i)) 16 where my_column = a_my_columns(i) 17 ; 18 exception 19 when e_forall_error then 20 for i in 1..sql%bulk_exceptions.count 21 loop 22 dbms_output.put_line(a_my_columns(sql%bulk_exceptions(i).error_index)); 23 end loop; 24 end; 25 / 2 4 PL/SQL procedure successfully completed.I have a series of update statements that I need to use in my Oracle package. EXCEPTION WHEN TOO_MANY_ROWS THEN (What do I tell the Procedure to do here, what am I able to tell it to do?It's rare but there may be an occasional and unavoidable user error that would result in one of the update statements throwing a "Single row sub-query returns one or more rows" Error. ) --end of updates that need the exception handling -- more insert statements into other tables based on data from the preceding Temp Table END; First, the TOO_MANY_ROWS exception will not catch the case where your select statements return multiple rows.
v_sql_errm); END; Is there any way within the exception block to determine the row/value on which the query is encountering an error?When executing the query manually it tells me how many rows were affected, I want to get that number in PL/SQL.The basic problem is that SQL%ROWCOUNT is a PL/SQL variable (or function), and cannot be directly accessed from an SQL command. 1 client updated for 10 After running update statement if you check the count on what you actually updated - This doesn't give any generic solution.Your answer doesn't explain that (look at the accepted answer, which does explain this).However, judging from the comments it looks as if your answer might be a good addendum to the accepted answer.