I cannot get the update working. the select works fine.
create or replace type rec_emp is object(
EMP_ID number,
EMP_F_N varchar2(20),
EMP_L_N varchar2(50),
DEPT number,
emp_salary number);
/
create or replace type T_EMP as table of rec_emp;
/
create or replace type rec_emp_upd is object (
EMP_F_N varchar2(20),
EMP_L_N varchar2(50),
emp_salary NUMBER);
/
create or replace type T_EMP_UPD as table of REC_EMP_UPD;
/
DECLARE
L_EMP T_EMP;
L_EMP_UPD T_EMP_UPD;
begin
select REC_EMP(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY) BULK COLLECT INTO L_EMP from employees;
forall I in L_EMP.first..L_EMP.last
update employees set salary = salary + 1000
where EMPLOYEE_ID = L_EMP(I).EMP_ID
RETURNING FIRST_NAME, LAST_NAME, SALARY
bulk collect into L_EMP_UPD;
For I in 1..L_EMP_UPD.COUNT
loop
DBMS_OUTPUT.PUT_LINE('New salary of '||L_EMP_UPD(I).FIRST_NAME || L_EMP_UPD(I).LAST_NAME ||' is '||L_EMP_UPD(I).SALARY);
END LOOP;
end;
/
Error report -
ORA-06550: line 12, column 149:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 12, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 53:
PLS-00302: component 'FIRST_NAME' must be declared
ORA-06550: line 16, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
Return an instance of rec_emp_upd:
returning rec_emp_upd ( first_name, last_name, salary )
bulk collect into l_emp_upd
Which gives:
declare
l_emp t_emp;
l_emp_upd t_emp_upd;
begin
select rec_emp (employee_id,first_name,last_name,department_id,salary)
bulk collect into l_emp
from hr.employees;
forall i in l_emp.first..l_emp.last
update hr.employees
set salary = salary + 1000
where employee_id = l_emp (i).emp_id
returning rec_emp_upd ( first_name, last_name, salary )
bulk collect into l_emp_upd ;
for i in 1..l_emp_upd.count loop
dbms_output.put_line ('New salary of '
|| l_emp_upd (i).emp_f_n
|| l_emp_upd (i).emp_l_n
|| ' is '
|| l_emp_upd (i).emp_salary);
end loop;
end;
/
New salary of DonaldOConnell is 3600
New salary of DouglasGrant is 3600
New salary of JenniferWhalen is 5400
New salary of MichaelHartstein is 14000
New salary of PatFay is 7000
...