It’s been quite a while that I wrote a post in blog.. .. here is the one I used to update large number of records based on bulk update concept in oracle 10g and wanted to share it with you all that how we can do bulk update when there are enormous records in a table to avoid space issues, wish this could help you all. Table name and column names are changed for confidentiality however the concept is fairly clear and simple.
Bulk Bind concept:
Method 1:
CREATE OR REPLACE PROCEDURE UPDATEA_ALL_ROWS IS
CURSOR CR IS SELECT OBJECT_ID FROM TEST1;
TYPE O_I IS TABLE OF TEST1.OBJECT_ID%TYPE INDEX BY BINARY_INTEGER;
OBJ_1 O_I;
CNT NUMBER:=0;
BEGIN
FOR REC IN CR LOOP
CNT:=CNT+1;
OBJ_1(CNT):=REC.OBJECT_ID;
END LOOP;
FORALL I IN 1..CNT
UPDATE TEST1 SET OBJECT_ID2=OBJ_1(I) WHERE OBJECT_ID=OBJ_1(I);
END;
method 2:
create or replace PROCEDURE update_all_rows (limit_in IN PLS_INTEGER) is
type segtab is table of test1%rowtype;
type instab is table of test1.object_id%type index by binary_integer;
l_segtab segtab;
l_instab instab;
cursor cr_rec is
select *
from test1;
begin
open cr_rec;
loop
fetch cr_rec bulk collect into l_segtab limit limit_in;
if l_segtab.count > 0 then
for i in l_segtab.first .. l_segtab.last loop
l_instab(i) := l_segtab(i).object_id;
end loop;
forall i in l_segtab.first .. l_segtab.last
update test1 set object_id2 = l_instab(i) where object_id=l_instab(i);
end if;
dbms_output.put_line ('commit performed');
commit;
exit when cr_rec%notfound;
end loop;
close cr_rec;
end update_all_rows;
Execution:
SQL> execute update_all_rows(1000);Method 3: using bulk binding and collections ( with no limit keyword)
commit performed
commit performed
commit performed
commit performed
commit performed
commit performed
PL/SQL procedure successfully completed.
create or replace PROCEDURE update_all_rows1 is
type test1_t is table of test1.object_id%type;
lst test1_t;
begin
select object_id bulk collect
into lst
from test1;
forall i in lst.first .. lst.last
update test1 set object_id2=lst(i) where object_id = lst(i);
end update_all_rows1;
please add anything that you would like to see here or anything else that makes this post complete... Have a Fun all the time....
