-- #############################################################################################
--
-- %Purpose: Performance Enhancements with PL/SQL DBMS_SQL Bulk-Operations instead of Looping
--
-- Use:      Needs Oracle DBA Access
--
-- #############################################################################################
--
DECLARE
  vCursor    INTEGER := DBMS_SQL.Open_Cursor;
  vCount     INTEGER;
  vStatement VARCHAR2(2000);
  vEmps      DBMS_SQL.Varchar2_Table;
BEGIN
  vStatement := 'UPDATE emp SET sal = sal + 1' ||
                ' WHERE ename = :ThisEname';
  vEmps(1) := 'KING';
  vEmps(2) := 'MILLER';
  vEmps(3) := 'HUBER';
  vEmps(4) := 'SCOTT';
  vEmps(5) := 'ADAMS';

  BEGIN
    DBMS_SQL.Parse(vCursor,vStatement, DBMS_SQL.Native);
    DBMS_SQL.Bind_Array(vCursor,'ThisEname',vEmps);
    vCount := DBMS_SQL.Execute(vCursor);
    DBMS_SQL.Close_Cursor(vCursor);
    DBMS_OUTPUT.PUT_LINE('vCount = ' || TO_CHAR(vCount));
  END;
END;