Updating the records in to the cursor Rabbid sex cam
I have highlighted the areas which is differ from compared with a normal cursor.Represents the entire set of records from a base table or the results of an executed command.There can be a situation where you have to use a cursor, even though the experts say not to use cursors or to avoid them as much as possible.But if you look closely, most of the time we use cursors to iterate through a row collection and update the same table.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.That way, all of the subroutines in this package could access the i'th element of the RECORD of TABLES we have defined.FOR profile_rec IN profile_cur LOOP Quo To Pur Days( profile_rec.personid , profile_rec. IF changed Flag = TRUE THEN UPDATE profile SET quotopurdays = profile_rec. WHERE personid = profile_rec.personid; changed Flag := FALSE; END IF; END LOOP; I tried to use the CURSOR loop to populate a PL table of profile People_rec.
This seems a messy way of getting around the problem. COUNT UPDATE profile SET quotopurdays = LTRIM(SUBSTR(people_tab(bulk Num),0,6)), maildays = LTRIM(SUBSTR(people_tab(bulk Num),7,9)), ... What we (well, you) want to do is ARRAY fetch N records, do your thing to them, and ARRAY update N records (or less). (i don't like that substr trick, just adds overhead). The following example shows how to accomplish this.
The Update is dependent on results of 19 procedures which are quite complex.
I can not do a one hit update outside of the cursor loop.
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.Note that we are fetching out the ROWID for updating (faster) and we are saving a changed flag as well [email protected] We update any empno with an ODD empno 20 procedure Quo To Pur Days( p_empno in number, p_ename in out NOCOPY varchar2, p_changed in out NOCOPY varchar2 ) 21 is 22 begin 23 if (mod(p_empno,2) = 1 ) 24 then 25 p_ename := initcap( p_ename ); 26 p_changed := 'Y'; 27 end if; 28 end; 29 now for the meat of the routine.Here we Array fetch N records (set l_array_size to the number of records you want to process. P_COMM; * ERROR at line 12: ORA-06550: line 12, column 4: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list ORA-06550: line 12, column 4: PL/SQL: SQL Statement ignored . May 13, 2003 - pm UTC sorry -- but seems obvious to me? record(n) vs a record of arrays a single record with attributes that happen to be arrays.OPEN profile_cur FOR sql Stmt; LOOP FETCH profile_cur INTO profile_rec; EXIT WHEN profile_cur%NOTFOUND; Quo To Pur Days( profile_rec.personid , profile_rec. IF changed Flag = TRUE THEN people_tab(x).personid := profile_rec.personid; people_tab(x).quotopurdays := profile_rec.quotopurdays; ...; ...; x := x 1; changed Flag := FALSE; END IF; END LOOP; CLOSE profile_cur; FORALL bulk Num IN 1..people_tab.