Bulk Collect and ForAll - Oracle

Question

I am looking for the syntax to access a column from the Record Type (Index by table). Below is the sample code. How shall i run the Update script in below Declare block which need empid from the V_Emprec record type. I have created a proc also which needs same parameter(empid).

Can this be done using %Rowtype or i need to create type with emp_stage.empid%type? If i create 2 TYPES for Empid and Ename as emp_stg.column_name%type, can i use those to replace the Insert script using Rowtype v_emprec?

Please tell the syntax to do this.

create table emp_master(empid number, ename varchar2(50));
create table emp_stage (empid number, ename varchar2(50));
create procedure update_emp_name(P_empid in emp_master.empid%type)
is
begin
Update emp_stage set ename =INITCAP(ename) WHERE EMPID =P_empid;
commit;
end;

Declare
Type emprec is table of emp_master%rowtype index by pls_integer;
v_emprec emprec;
Begin
Select empid,ename bulk collect into v_emprec from emp_master;
ForAll i in 1..v_emprec.count
Insert into emp_stage values v_emprec(i);
Update emp_stage set ename =INITCAP(ename) WHERE EMPID =v_emprec.empid(i);
 /*Need Correct Syntax to use empid from the v_emprec type*/           
update_emp_name(); 
commit;
End;

Thanks

Answer 1

You can do the update in a second forall, and reference the record field as you would any record field or table column; you just have the index reference in the wrong place:

ForAll i in 1..v_emprec.count
  Insert into emp_stage values v_emprec(i);
ForAll i in 1..v_emprec.count
  Update emp_stage set ename = INITCAP(ename)
  WHERE EMPID = v_emprec(i).empid;

You can't call a procedure with forall, it only allows DML. You can use a normal for loop though:

for i in 1..v_emprec.count loop         
  update_emp_name(v_emprec(i).empid);
end loop;

But as that does single row-by-row updates, and incurs extra context switches, that will be less efficient than the forall approach; or indeed a single update of all the rows. You can also loop around the collection and initcap the fields before doing the insert:

for i in 1..v_emprec.count loop         
  v_emprec(i).ename := INITCAP(v_emprec(i).ename);
end loop;
ForAll i in 1..v_emprec.count
  Insert into emp_stage values v_emprec(i);

Or change the insert to refer to the fields separately and do the initcap during the insert (which also won't work with 10g). Or do the initcap in the query:

Select empid, INITCAP(ename) bulk collect into v_emprec from emp_master;
ForAll i in 1..v_emprec.count
  Insert into emp_stage values v_emprec(i);