Oracle if updating then

Rated 3.98/5 based on 573 customer reviews

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.

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.

We might ask for 100 rows -- our last fetch will get say 55 rows -- notfound will be set but we need to process those last 55 FIRST before we exit! l_record.empno.count 53 loop 54 Quo To Pur Days( l_record.empno(i), l_record.ename(i), l_record.changed(i) ); 55 /* ..... COM select empno, mod(empno,2), ename from emp; EMPNO MOD(EMPNO,2) ENAME ---------- ------------ ---------- 7369 1 Smith 7499 1 Allen 7521 1 Ward 7566 0 JONES 7654 0 MARTIN 7698 0 BLAKE 7782 0 CLARK 7788 0 SCOTT 7839 1 King 7844 0 TURNER 7876 0 ADAMS 7900 0 JAMES 7902 0 FORD 7934 0 MILLER 14 rows selected. ) but most importantly declare it based on the structure of the cursor for ease of maintenance down the road: 5 type emp Rec is record 6 ( 7 empno dbms_sql.number_table, 8 ename dbms_sql.varchar2_table, 9 job dbms_sql.varchar2_table, 10 mgr dbms_sql.number_table, 11 hiredate dbms_sql.date_table, 12 sal dbms_sql.number_table, 13 comm dbms_sql.number_table, 14 deptno dbms_sql.number_table, 15 rowid rowid Array, 16 changed dbms_sql.varchar2_table 17 ); becomes: TYPE emp Tab IS TABLE OF c%ROWTYPE; l_records emp Tab; And then change the fetch: 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 to FETCH c BULK COLLECT INTO l_records ... COM declare 2 type emp Tab is table of number index by binary_integer; 3 l_records emp Tab; 4 cursor c is select empno from emp; 5 begin 6 open c; 7 fetch c bulk collect into l_records; 8 close c; 9 end; 10 / PL/SQL procedure successfully completed. I presume it is declaration of pl/sql table in record type? COMM%TYPE); 7 VREC EMPREC; 8 CURSOR C1 IS SELECT EMPNO, EMPNM, DEPT, SALARY, COMM FROM T_EMP; 9 BEGIN 10 OPEN C1; 11 LOOP 12 FETCH C1 BULK COLLECT INTO VREC. The values for the columns come from different sources. two sessions cannot select the same row for update. After validations I have a couple of table types which stores the (1)error flag and success falg against the (2)record id's. Within each bulk block or only one commit at the end? Within each bulk block or only one commit at the end? if your code is just what you have there - you should be able to answer that. What if after the 25th loop the code fails (ora-1555 for example, but could be anything). VOUCHERID limit 1000; l_done := c_update_fact_vouchers%notfound; -- open cursor of records in temporary table which contains updated information open c_update_vouchers; loop forall i in 1 .. VOUCHERID.count update FACT_PREP_V_BONITA_small upd set upd.status = c_update_vouchers.tmp5_status(i), upd.dateused = c_update_vouchers.tmp6_dateused(i), upd.locked = c_update_vouchers.tmp7_locked(i), upd.lockedreason = c_update_vouchers.tmp8_lockedreason(i), upd.msisdn = c_update_vouchers.tmp9_msisdn(i), upd.pstatus = c_update_vouchers.tmp10_pstatus(i), upd.pstransaction = c_update_vouchers.tmp11_pstransaction(i), upd.plocked = c_update_vouchers.tmp12_plocked(i), upd.pltransaction = c_update_vouchers.tmp13_pltransaction(i) where rowid = l_update_vouchers. we need to do this without update - there are not enough hours in the day. VOUCHERID.count update FACT_PREP_V_BONITA_small upd set upd.status = c_update_vouchers.tmp5_status(i), upd.dateused = c_update_vouchers.tmp6_dateused(i), upd.locked = c_update_vouchers.tmp7_locked(i), upd.lockedreason = c_update_vouchers.tmp8_lockedreason(i), upd.msisdn = c_update_vouchers.tmp9_msisdn(i), upd.pstatus = c_update_vouchers.tmp10_pstatus(i), upd.pstransaction = c_update_vouchers.tmp11_pstransaction(i), upd.plocked = c_update_vouchers.tmp12_plocked(i), upd.pltransaction = c_update_vouchers.tmp13_pltransaction(i) where rowid = l_update_vouchers.

*/ 56 end loop; there we just do your 19 complex functions.... l_record.empno.count 59 update emp 60 set empno = l_record.empno(i), 61 ename = l_record.ename(i), 62 job = l_record.job(i), 63 mgr = l_record.mgr(i), 64 hiredate = l_record.hiredate(i), 65 sal = l_record.sal(i), 66 comm = l_record.comm(i), 67 deptno = l_record.deptno(i) 68 where rowid = l_record.rowid(i) 69 and l_record.changed(i) = 'Y'; 70 we update the records you need to have updated by rowid. cheers, Scott declare 2 type emp Tab is table of emp%rowtype index by binary_integer; 3 l_records emp Tab; 4 cursor c is select * from emp; 5 begin 6 open c; 7 fetch c bulk collect into l_records; 8 close c; 9 end; 10 / fetch c bulk collect into l_records; * ERROR at line 7: ORA-06550: line 7, column 28: PLS-00597: expression 'L_RECORDS' in the INTO list is of wrong type ORA-06550: line 7, column 2: PL/SQL: SQL Statement ignored [email protected] January 08, 2002 - am UTC Instead of defining my own types -- I borrowed the definitions from dbms_sql: -- Bulk SQL Types -- type Number_Table is table of number index by binary_integer; type Varchar2_Table is table of varchar2(2000) index by binary_integer; type Date_Table is table of date index by binary_integer; .... the "of col1, col2" is used to removed ambiguities in the event the query references more then one table. declare 2 type record_t is record 3 ( 4 id number, 5 name varchar2(30) 6 ); 7 8 type arr_t is table of record_t; 9 arr ARR_T; 10 begin 11 select * bulk collect into arr from rec; 12 for i in 1..100 loop 13 dbms_output.put_line('id='

I can not do a one hit update outside of the cursor loop. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status); -- No matched clause, insert only.MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status); -- No not-matched clause, update only.MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status; -- Both clauses present.MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHERE b.status !

Leave a Reply