0 Replies Latest reply: May 1, 2012 1:11 PM by 888665 RSS

    Bulk collect usage in cursor for loop

    888665
      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