This content has been marked as final. Show 5 replies
Suspect because 'LN' is a built-in function.
If you want to use FOR ALL:
But the whole PL/SQL block can be replaced by a plain UPDATE.
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; /
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.
Edited by: Twinkle on Oct 27, 2009 5:23 PM
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.
If per_all_people_f has a PK or unique index on employee_number, then you can do:
If not, 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
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.
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)