CREATE OR REPLACE PROCEDURE Proc1 
     (p_error  OUT NUMBER) 
IS 
  TYPE rec_1 IS RECORD(ename emp.ename%TYPE, 
                        dname dept.dname%TYPE); 
  TYPE t_rec IS TABLE OF REC_1 INDEX BY BINARY_INTEGER ; 
  v_rec         T_REC; 
  CURSOR cur1 IS 
    SELECT ename, 
           dname 
    FROM   emp 
           INNER JOIN dept 
             ON emp.deptno = dept.deptno; 
  v_tablecount  INTEGER; 
BEGIN 
  dbms_output.Put_line('dept table update begins ' 
                       ||To_char(SYSDATE,'MM/DD/YYYY HH:MI:SS')); 
   
  OPEN cur1; 
   
  FETCH cur1 BULK COLLECT INTO v_rec LIMIT 10000; 
   
  v_tablecount := v_rec.COUNT; 
   
  IF v_rec > 0 THEN 
    FORALL i IN 1..v_rec.COUNT 
      UPDATE dept 
      SET    found_ind = 'Y', 
             last_updated_date = SYSDATE 
      WHERE  dname = v_rec.Dname(i); 
     
    FORALL i IN 1..v_rec.COUNT 
      UPDATE emp 
      SET    dname = v_rec.Dname(i), 
             found_ind = 'Y', 
             last_updated_date = SYSDATE 
      WHERE  ename = v_rec.Ename(i); 
  END IF; 
   
  CLOSE c_perfdata; 
   
  dbms_output.Put_line('Dept table update complete ' 
                       ||To_char(SYSDATE,'MM/DD/YYYY HH:MI:SS')); 
   
  dbms_output.Put_line('emp table update complete ' 
                       ||To_char(SYSDATE,'MM/DD/YYYY HH:MI:SS')); 
   
  p_error := 0; 
EXCEPTION 
  WHEN OTHERS THEN 
    dbms_output.Put_line('Run Date: ' 
                         ||To_char(SYSDATE,'yyyymmdd') 
                         ||'  Error: ' 
                         ||SQLCODE 
                         ||'-' 
                         ||Substr(SQLERRM,1,200)); 
     
    p_error := SQLCODE; 
END;