Create local copy of HR.EMPLOYEES

DECLARE 
   c_schema_name   CONSTANT VARCHAR2 (100) := 'HR'; 
   c_table_name    CONSTANT VARCHAR2 (100) := 'EMPLOYEES'; 
   e_no_such_table          EXCEPTION; 
   PRAGMA EXCEPTION_INIT (e_no_such_table, -942); 
BEGIN 
   BEGIN 
      EXECUTE IMMEDIATE 'DROP TABLE ' || c_table_name; 
   EXCEPTION 
      WHEN e_no_such_table 
      THEN 
         NULL; 
   END; 
 
   EXECUTE IMMEDIATE 
         'CREATE TABLE ' 
      || c_table_name 
      || ' AS SELECT * FROM ' 
      || c_schema_name 
      || '.' 
      || c_table_name; 
END;

Who's got a big fat $10000 salary now?

SELECT *  
  FROM employees 
 WHERE salary = 10000

INDICES OF Example: Bind array used in clause

DECLARE  
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE  
                           INDEX BY PLS_INTEGER;  
  
   l_employees   employee_aat;  
BEGIN  
   l_employees (1) := 137;  
   l_employees (100) := 126;  
   l_employees (500) := 147;  
  
   FORALL l_index IN INDICES OF l_employees  
      UPDATE employees  
         SET salary = 10000  
       WHERE employee_id = l_employees (l_index);  
  
   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);  
END; 

Now how many employees have this salary?

SELECT *  
  FROM employees  
 WHERE salary = 10000 

Recreate Table for Next Example

BEGIN 
   EXECUTE IMMEDIATE 'DROP TABLE employees'; 
   EXECUTE IMMEDIATE 'CREATE TABLE employees AS SELECT * FROM hr.employees'; 
END;

INDICES OF Example: Non-bind array used in clause

DECLARE  
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE  
                           INDEX BY PLS_INTEGER;  
  
   l_employees          employee_aat;  
  
   TYPE boolean_aat IS TABLE OF BOOLEAN  
                          INDEX BY PLS_INTEGER;  
  
   l_employee_indices   boolean_aat;  
BEGIN  
   l_employees (1) := 137;  
   l_employees (100) := 126;  
   l_employees (500) := 147;  
   --  
   l_employee_indices (1) := FALSE;  
   l_employee_indices (500) := TRUE;  
   l_employee_indices (799) := NULL;  
  
   FORALL l_index IN INDICES OF l_employee_indices   
      BETWEEN 1 AND 600  
      UPDATE employees  
         SET salary = 10000  
       WHERE employee_id = l_employees (l_index);  
 
   DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT); 
END; 

And now how many employees have a salary of 10000?

SELECT *  
  FROM employees  
 WHERE salary = 10000 

Impact on BULK%ROWCOUNT

DECLARE 
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE 
      INDEX BY PLS_INTEGER; 
 
   l_employees          employee_aat; 
 
   TYPE boolean_aat IS TABLE OF BOOLEAN 
      INDEX BY PLS_INTEGER; 
 
   l_employee_indices   boolean_aat; 
BEGIN 
   l_employees (1) := 137; 
   l_employees (100) := 126; 
   l_employees (500) := 147; 
   -- 
   l_employee_indices (1) := FALSE; 
   l_employee_indices (500) := TRUE; 
   l_employee_indices (799) := NULL; 
 
   FORALL indx IN INDICES OF l_employee_indices BETWEEN 1 AND 600 
      UPDATE employees 
         SET salary = 10000 
       WHERE employee_id = l_employees (indx); 
 
   DBMS_OUTPUT.put_line ('Updated: ' || SQL%ROWCOUNT); 
 
   FOR indx IN 1 .. l_employees.COUNT 
   LOOP 
      BEGIN 
         DBMS_OUTPUT.put_line ('Index ' || indx || ' updated: ' || SQL%BULK_ROWCOUNT (indx)); 
      EXCEPTION 
         WHEN OTHERS 
         THEN 
            DBMS_OUTPUT.put_line ('Index ' || indx || ' failure: ' || SQLERRM); 
      END; 
   END LOOP; 
 
   FOR indx IN 1 .. 500 
   LOOP 
      BEGIN 
         DBMS_OUTPUT.put_line ('Index ' || indx || ' updated: ' || SQL%BULK_ROWCOUNT (indx)); 
      EXCEPTION 
         WHEN OTHERS 
         THEN 
            NULL; -- DBMS_OUTPUT.put_line ('Index ' || indx || ' failure: ' || SQLERRM); 
      END; 
   END LOOP; 
END;