Hi,
First attempt in ages at doing bulk collect/forall. I get an error on creating the package: ORA-00984 column not allowed here. Line with the error is the last field of the insert. (I note it below.) Any ideas? Thanks.
CREATE OR REPLACE PROCEDURE schemax."proca" (
p_term VARCHAR2,
p_week VARCHAR2)
AS
p_count_orig number;
p_count_final number;
bulk_fetch_limit number := 1000;
cursor get_headcount is
select distinct ID,
WEEK,
LDATE from
( select ID,
WEEK,
LDATE,
row_number() Over (Partition by period, lweek, uid Order By
Period, lweek, uid, campus_d desc nulls last) as loadrank
from schemax.table1
where PERIOD = p_term
and lweek = p_week )
where loadrank = 1;
type student_headcount_table is table of get_headcount.id%rowtype;
list_student_headcount_ids student_headcount_table;
BEGIN
open get_headcount;
loop
fetch get_headcount bulk collect into list_student_headcount_ids limit bulk_fetch_limit;
exit when get_headcount%notfound;
forall i in 1..list_student_headcount_ids.count
insert into odumgr.odu_student_headcount_all values
(ID,
LWEEK,
LDATE, ** says column not allowed here
NULL, --reporting1 items
NULL );
exit when get_headcount%notfound;
end loop;
commit;
close get_headcount;
END;
/