Updating multiple fields in oracle
I can not do a one hit update outside of the cursor loop.
The example I have seen show only one line command after FOR ALL so its good to know that FOR ALL can handle a multi line transaction with END LOOP. (calling some procedures / functions) inserting into another table end loop; It processes 6 lakhs records. DEPTNO,3); 40 END; 41 END LOOP; 42 END; 43 / PL/SQL procedure successfully completed. COUNT UPDATE emp SET sal=NULL WHERE emp_id=t_id(i); FORALL i IN 1 .. COUNT DELETE FROM dept WHERE emp_id=t_id(i); Regards, Ammu CREATE OR REPLACE procedure proc(p_trade_dt in number) is cursor cur_a is select val ,tradedate ,trade_dt_key ,(price1 price2) price ,((price1 price2)/2 mid_price ,decode( typ, 'B', QTY, 0 ) buy ,decode( typ, 'S', QTY, 0 ) sell ,(decode( type, 'B', QTY, 0 ) decode( typ, 'S', QTY, 0 )) buy_sell from price_data where trade_dt_key=p_trade_dt; cursor cur_b is select * from price_new where trade_dt_key = p_trade_dt; type price_type is table of cur_a%ROWTYPE; price_tab price_type; begin open cur_a; fetch cur_a bulk collect into price_tab; close cur_a; FORALL i IN 1 .. COUNT update price_new pn set price1 = -- sum(mid_prc) for the time period in --where clause where val = price_tab(i)and (pn.last_minus_5 January 24, 2009 - pm UTC I cannot evaluate code that doesn't make sense or compile. oh, and if you expect "sample code", I expect 'create table' Hi tom, Cant we use WHEN clause in FORALL during UPDATE also. l_fileid.count insert when (address_flag='Y') then into emp_addr( address, city, empno ) values ( address, city, s.currval ) select l_lname(i) lname, l_fname(i) fname, l_address(i) address, l_city(i) city, l_phone(i) phone1,l_address_flag(i) address_flag, l_phone_flag(i) phone_flag from dual; Thanks. I want to update based on when clause in a single table. It has nothing to do with performance, everything to do with "what is your transaction, your logical unit of work. I do not know what your data looks like, what your process is like, what your update involves, how your update works. I did a search for why use packages here on asktom and on the first page of hits was: Procedure vs Package Hello, could the use of rowids in this case be potentially dangerous? I was also thinking of maybe truncating the table and rather inserting the records.