Execute Immediate Update returns Bulk Collect

Execute Immediate Update returns Bulk Collect

                        This tutorial explains how to use update command with Execute Immediate which returns Bulk collect.

Q) Update Stock Manager or Stock Clerk  by 20%  and display updated employee's last name.

-- Declare a table type/indexed table which holds stock manager and stock clerk's last name

--Declare variable for storing sql command here Update.

-- Use Execute command for update.

--Display stock manager/clerk last name

This example updates Stock manager and Stock clerk salary by 20% using Execute Immediate and returns their last name.

declare
type typeemployees is table of employees.last_name%type;
v_employee typeemployees;
sql_stmt varchar2(250);

pct number:=20;
 

begin

sql_stmt := 'update employees set salary =salary+(salary*20/100) where job_ID in(''ST_MAN'',''ST_CLERK'')   '||
                    'RETURNING last_name INTO  :2';
dbms_output.put_line(sql_stmt);
 

EXECUTE IMMEDIATE sql_stmt RETURNING BULK COLLECT INTO v_employee;
 

FOR I IN v_employee.FIRST..v_employee.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(v_employee(I));
END LOOP;

END;

SQL>set Serveroutput on;

Execute the above PL/SQL block using /

SQL>/

update employees set salary =salary+(salary*20/100) where job_ID
in('ST_MAN','ST_CLERK')   RETURNING last_name INTO  :2
Weiss
Fripp
Kaufling
Vollman
Mourgos
Nayer
Mikkilineni
Landry
Markle
Bissot
Atkinson
Marlow
Olson
Mallin
Rogers
Gee
Philtanker
Ladwig
Stiles
Seo
Patel
Rajs
Davies
Matos
Vargas

PL/SQL procedure successfully completed.

TAGS:Execute Immediate Update returns Bulk Collect, Update command with dynamic SQL which returns bulk collect, Bulk collection from Execute Immediate, How to get bulk collect from Execute Immediate Update operation, Update for Execute immediate which returns Collection.