This discussion is archived
0 Replies Latest reply: May 1, 2012 11:11 AM by 888665 RSS

Bulk collect usage in cursor for loop

888665 Newbie
Currently Being Moderated
Hi Team,

I have one cursor like below assuming cursor is having 3000 records,

CURSOR csr_del_frm_stg(c_source_name VARCHAR2 , c_file_type VARCHAR2)
IS
SELECT stg.last_name,stg.employee_number,stg.email
FROM akam_int.xxak_eb_contact_stg stg
MINUS
SELECT ss.last_name,ss.employee_number,ss.email
FROM akam_int.xxak_eb_contact_stg_ss ss;

I declared one record type variable as,

TYPE emp_rec IS RECORD (LAST_NAME             VARCHAR2(40)
*,EMPLOYEE_NUMBER VARCHAR2(50)*
*,EMAIL VARCHAR2(80)*
*);*
TYPE emp_rec_ss IS VARRAY(3000) OF emp_rec;

Im updating the status of those cursor records to 'C' in the below for loop,

FOR l_csr_del_frm_stg IN csr_del_frm_stg(p_source_name , p_file_type)
LOOP
FETCH csr_del_frm_stg BULK COLLECT INTO emp_rec_ss LIMIT 500;

FORALL i IN emp_rec_ss.FIRST..emp_rec_ss.LAST

UPDATE akam_int.xxak_eb_contact_stg stg
SET akam_status_flag    = 'C'
WHERE stg.employee_number = emp_rec_ss(i).employee_number;

EXIT WHEN csr_del_frm_stg%NOTFOUND;
END LOOP;


Getting following errors if i compile the code,


PLS-00321: expression 'EMP_REC_SS' is inappropriate as the left hand side of an assignment statement
PLS-00302: component 'FIRST' must be declared

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points