Use numbers between say 1 to see what works best for you. ) 30 procedure do_it 31 is 32 l_record emp Rec; 33 l_array_size number default 5; 34 l_done boolean; 35 l_cnt number default 1; 36 37 cursor c is select emp.*, rowid, 'N' from emp; 38 begin 39 40 open c; 41 loop you might want to use dbms_application_info. One would use a record of arrays in 8i to facilitate bulk collects whilst using a record.SET_SESSION_LONGOPS (if you have my book, i have a good example of the use in there). COM select username, client_info 2 from v$session 3 where username = user; USERNAME ------------------------------ CLIENT_INFO -------------------------------------------------- OPS$TKYTE processing 11 thru 15 and that shows how to see "where" you are in the processing - -you can run this query repeatedly from another session whilst this procedure runs. Swell example of just how bulk collects, collections and bulk insert/updates ought to interact. P_COMM; 13 EXIT WHEN C1%NOTFOUND; 14 END LOOP; 15 DBMS_OUTPUT. Hi Tom, Is it possible to bulk append to a collection.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.
However, for faster performance and reliable field order, it is recommended that the list of fields be narrowed to only those that are actually needed. Additional information can be accessed using tokens (such as An optional expression that limits the records returned.The advantage to that is various tools use that table to show progress -- as well, it will estimate the time to completion for you when possible. For maintainability, and since I'm a lazy typist, I'd make a couple of changes to the code pro-forma. I've tried several times to manage this but couldn't. PUT_LINE('STATUS IS OK'); 16* END; 17 / FETCH C1 BULK COLLECT INTO VREC. Say I have a collection x that already has 5 elements populated. Is there a way to avoid, in the case of nested table, doing loop x.extend; x(i):=something; end loop; Obviouly if I do a select BULK COLLECTION INTO x again, all the 5 existing elements will be over written. Table A ID -- Primary key col1 col2 col3 col4 col5 Now Session 1: select 1 into var1 where id = 1 for update; -- can we say select 1 into var2 where id = 1 for update of col1, col2 ? update a set col = xxx, col2=yyyy where id = 1; Session 2: select 1 into var1 where id = 1 for update; update a set col4=zzzz, col5=qqqq where id = 1; Now, when these are executed simultaneouly, though on the same row for DIFFERENT COLUMNS, how to resolve this locking issue. I have a thought of using this when I'm doing a conversion program.Just setting client info though gives you the ability from another session to "see" where you are in your processing, I'll show you the query later 42 dbms_application_info.set_client_info 43 ( 'processing ' || l_cnt || ' thru ' || (l_cnt l_array_size-1) ); 44 Ok, here we bulk fetch N records into our arrays -- including the rowids and an array of changed flags -- all 'N' to begin with: 45 fetch c bulk collect into l_record.empno, l_record.ename, l_record.job, 46 l_record.mgr, l_record.hiredate, 47 l_record.sal, l_record.comm, l_record.deptno, 48 l_record.rowid, l_record.changed 49 LIMIT l_array_size; 50 l_done := c%notfound; we need to save this boolean, so we can exit the loop at the end. In lieu of your record of dbms_sql.*_table's I'd use a PL/SQL table (or perhaps varray? Could you please just explain dbms_sql.number_table . We have these update statements in our batch jobs that get kicked off every night at same time. May 15, 2003 - am UTC ROWS are locked, not columns. I picked up all the data from stage tables into plsql table types using bulk collect.For more information on WHERE clauses and SQL statements, see Building a query expression and Specifying a query in Python.(The default value is None).
An SQL prefix clause is positioned in the first position and will be inserted between the SELECT keyword and the SELECT COLUMN LIST.
WHERE personid = LTRIM(SUBSTR(people_tab((bulk Num),50,9); How can you use FORALL to update many rows using a PL table, Collection etc? Do you agree that FORALL is quicker than an update in the cursor loop? If you wanted, you could make the L_RECORD variable a package GLOBAL (define it in the body, not in a procedure in the body) and just pass the INDEX around from subroutine to subroutine.