Skip to Main Content
  • Questions
  • Bulk update with returning bulk collect failing with ORA-00947

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Samuel.

Asked: November 30, 2019 - 1:05 am UTC

Last updated: December 05, 2019 - 10:06 am UTC

Version: 12

Viewed 1000+ times

You Asked

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.

and Chris said...

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
...


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library