5 Replies Latest reply on Oct 27, 2009 1:21 PM by John Spencer

    Bulk Select and FOR ALL update not working

    MSK
      Hi
      I am trying to run this but it does not work. I get this error
      PLS-00430: FORALL iteration variable I is not allowed in this context

      declare
      cursor c is
      select employee_number, national_identifier,
      last_name, first_name
      from per_all_people_f;

      type en_tab is table of per_all_people_f.employee_number%type;
      type ni_tab is table of per_all_people_f.national_identifier%type;
      type ln_tab is table of per_all_people_f.last_name%type;
      type fn_tab is table of per_all_people_f.first_name%type;
      en en_tab;
      ni ni_tab;
      ln ln_tab;
      fn fn_tab;

      begin
      open c;
      loop
      fetch c bulk collect into en, ni, ln,fn;
      forall i in 1..en.count
      update xxdl_hr_people_interface
      set first_name = fn(i),last_name = ln(i),national_identifier = ni(i)
      where employee_number = en(i);

      exit when c%notfound;
      end loop;
      close c;
      end;
      /

      But if I dont try to update first_name ,last_name, national_identifer at the same time and modify the block to be like this, it runs

      declare
      cursor c is
      select employee_number, national_identifier,
      last_name, first_name
      from per_all_people_f;

      type en_tab is table of per_all_people_f.employee_number%type;
      type ni_tab is table of per_all_people_f.national_identifier%type;
      type ln_tab is table of per_all_people_f.last_name%type;
      type fn_tab is table of per_all_people_f.first_name%type;
      en en_tab;
      ni ni_tab;
      ln ln_tab;
      fn fn_tab;

      begin
      open c;
      loop
      fetch c bulk collect into en, ni, ln,fn;
      forall i in 1..en.count
      update xxdl_hr_people_interface
      set first_name = fn(i)
      where employee_number = en(i);

      exit when c%notfound;
      end loop;
      close c;
      end;
      /

      Any pointers on why this does not work?


      Thank you
      Kumar
        • 1. Re: Bulk Select and FOR ALL update not working
          94799
          Suspect because 'LN' is a built-in function.
          • 2. Re: Bulk Select and FOR ALL update not working
            Solomon Yakobson
            If you want to use FOR ALL:
            declare
                type en_tab is table of per_all_people_f.employee_number%type;
                type ni_tab is table of per_all_people_f.national_identifier%type;
                type ln_tab is table of per_all_people_f.last_name%type;
                type fn_tab is table of per_all_people_f.first_name%type;
                en en_tab;
                ni ni_tab;
                ln ln_tab;
                fn fn_tab;
            begin
                select  employee_number,
                        national_identifier, 
                        last_name,
                        first_name
                  bulk collect into en,
                                    ni,
                                    ln,
                                    fn
                  from  per_all_people_f;
                forall i in 1..en.count
                  update xxdl_hr_people_interface
                    set first_name = fn(i) 
                    where employee_number = en(i); 
            end;
            /
            But the whole PL/SQL block can be replaced by a plain UPDATE.

            SY.
            • 3. Re: Bulk Select and FOR ALL update not working
              Twinkle
              ln is a built in function for finding the n atural logarithm of an expression.

              change the ln to lname.. It works.

              using little different data.
              SQL> declare
                2  cursor c is
                3  select empno, ename, 
                4  job, deptno 
                5  from emp;
                6  type en_tab is table of emp.empno%type;
                7  type ni_tab is table of emp.ename%type;
                8  type ln_tab is table of emp.job%type;
                9  type fn_tab is table of emp.deptno%type;
               10  en en_tab;
               11  ni ni_tab;
               12  lname ln_tab;
               13  fn fn_tab;
               14  begin
               15  open c;
               16  loop
               17  fetch c bulk collect into en, ni, lname,fn;
               18  forall i in 1..en.count
               19  update emp_same
               20  set deptno = fn(i),job = lname(i),ename = ni(i)
               21  where empno = en(i); 
               22  exit when c%notfound;
               23  end loop;
               24  close c;
               25  end;
               26  /
              
              PL/SQL procedure successfully completed.
              Twinkle

              Edited by: Twinkle on Oct 27, 2009 5:23 PM
              • 4. Re: Bulk Select and FOR ALL update not working
                MSK
                Hi SY:
                How do I achieve that? Without looping through a cursor, is it possible. I am trying to optimize this sql that is running for a very long time.

                Thank you
                Kumar
                • 5. Re: Bulk Select and FOR ALL update not working
                  John Spencer
                  If per_all_people_f has a PK or unique index on employee_number, then you can do:
                  UPDATE (SELECT pi.first_name, pi.last_name, pi.national_identifier,
                                 pf.first_name pffirst, pf.last_name pflast, 
                                 pf.national_identifier pfnat
                          FROM xxdl_hr_people_interface pi, per_all_people_f pf
                          WHERE pf.employee_number = pi.employee_number)
                  SET first_name = pffirst,
                      last_name = pflast,
                      national_identifeir = pfnat
                  If not, you can do:
                  UPDATE xxdl_hr_people_interface pi
                  SET (first_name, last_name, national_identifier) = 
                              (SELECT first_name, last_name, national_identifier
                               FROM per_all_people_f pf
                               WHERE pf.employee_number = pi.employee_number)
                  WHERE EXISTS (SELECT 1
                                FROM per_all_people_f pf
                                WHERE pf.employee_number = pi.employee_number)
                  Note that if there might be more than one row in per_all_people_f for an employee_id, then you need to modify the select sub-query to add a predicate to make sure it only returns one row for each employee_number.

                  John