Here is my code:
procedure load_cmpnt_history is
cursor c1 is
select --+rule
h.ndc_emp_id
, nvl(upper(h.cmpnt_id),'FAA00000') cmpnt_id
, h.compl_dt
, nvl(nvl(h.crs_grade,h.cmpl_stat),'OTHER') crs_grade
, h.cmpl_stat
, h.lst_upd_tstmp
, h.cmpnt_typ
, h.rev_dt
, s.lob_cd
, c.cmpnt_title
from lms_stud_cmpnt_hst h
, app_reference.student_base_ref s
, lms_cmpnt c
where h.ndc_emp_id is not null
and h.stud_id is not null
and s.ndc_emp_id (+) = h.ndc_emp_id
and h.cmpnt_id = c.cmpnt_id(+)
and h.rev_dt = c.rev_dt(+)
and h.cmpnt_typ = c.cmpnt_typ(+);
r1 c1%rowtype;
grade_temp varchar2(30);
grade_type_temp varchar2(1);
begin
delete from cmpnt_history;
sys.dbms_output.put_line(to_char(sysdate,'mm/dd/yyyy hh24:mi:ss'));
for r1 in c1 loop
begin
app_reference.retrieve_grade
(r1.cmpl_stat,r1.crs_grade,grade_temp,grade_type_temp,r1.lob_cd);
insert into cmpnt_history
( ndc_emp_id
, cmpnt_id
, cmpnt_type
, rev_dt
, compl_dt
, crs_grade
, cmpnt_title
, crs_grade_type
, lst_upd_tstmp )
values
( r1.ndc_emp_id
, r1.cmpnt_id
, r1.cmpnt_typ
, r1.rev_dt
, r1.compl_dt
, grade_temp
, r1.cmpnt_title
, grade_type_temp
, r1.lst_upd_tstmp );
exception when others then
sql_code := sqlcode;
sql_error := substr(sqlerrm,1,4000);
insert into calculation_log
(table_name,oracle_errorcode,oracle_errormsg)
values
('CMPNT_HISTORY',sql_code,sql_error);
end;
end loop;
commit;
exception
when others then
sql_code := sqlcode;
sql_error := substr(sqlerrm,1,4000);
err := 1;
rollback;
insert into calculation_log
(table_name,oracle_errorcode,oracle_errormsg)
values
('CMPNT_HISTORY',sql_code,sql_error);
commit;
end;How can I incorporate bulk collect to make the insert much faster?
It depends on whether you want to limit the rows bulk collected or not. But in either case you need to replace:
for r1 in c1 loop
And declare an array variable to hold the data. e.g.:
type cur_arr is table of c1%rowtype index by pls_integer;
arr cur_arr;
If you're happy with unlimited bulk collection, you can do something like:
select ...
bulk collect
into arr
from your_query
This can blow out your PGA if it returns a large number of rows. So you may want to limit the rows you fetch at a time with something like:
open c1;
loop
fetch c1 bulk collect into arr limit 100;
exit when arr.count = 0;
...
end loop;
close c1;
You can read more about bulk collect at:
http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html