10g_forall.sql

DROP TABLE favorites;

CREATE TABLE favorites (
   flavor VARCHAR2(100),
   NAME VARCHAR2(100));

DECLARE
   TYPE favorites_tt IS TABLE OF favorites%ROWTYPE
      INDEX BY PLS_INTEGER;

   TYPE guide_tt IS TABLE OF PLS_INTEGER
      INDEX BY PLS_INTEGER;

   family   favorites_tt;
   guide    guide_tt;
   l_count INTEGER;
   
   PROCEDURE cleanup IS 
   BEGIN
      DELETE FROM favorites;
      guide.DELETE;
   END;
BEGIN
   family (1).flavor := 'CHOCOLATE';
   family (1).NAME := 'VEVA';
   family (25).flavor := 'STRAWBERRY';
   family (25).NAME := 'STEVEN';
   family (500).flavor := 'VANILLA';
   family (500).NAME := 'CHRIS';
   family (5000).flavor := 'ROCKY ROAD';
   family (5000).NAME := 'ELI';
   family (5001).flavor := 'PINEAPPLE';
   family (5001).NAME := 'MOSHE';
   family (5002).flavor := 'EVERYTHING';
   family (5002).NAME := 'MICA';   
   
   -- Just a subset of the family favorites...
   guide (1) := 1;
   guide (5000) := 2;
   FORALL indx IN indices OF guide -- bewteen my_list.first .. my_first.last
      INSERT INTO favorites
           VALUES family (indx);
           
   SELECT COUNT(*) into l_count FROM favorites;           
   DBMS_OUTPUT.PUT_LINE (l_count); 
   
   cleanup;         
   
   -- Insert nothing at all: no error raised!
   FORALL indx IN indices of guide -- bewteen my_list.first .. my_first.last
      INSERT INTO favorites
           VALUES family (indx);
           
   SELECT COUNT(*) into l_count FROM favorites;           
   DBMS_OUTPUT.PUT_LINE (l_count);     
   
   cleanup;         
   
   -- Use BETWEEN clause
   guide (25) := 1;
   guide (500) := 1;
   guide (5000) := 1;   
   FORALL indx IN indices of guide BETWEEN guide.FIRST AND LEAST (guide.LAST, 500)
      INSERT INTO favorites
           VALUES family (indx);
           
   SELECT COUNT(*) into l_count FROM favorites;           
   DBMS_OUTPUT.PUT_LINE (l_count);    
   
   cleanup; 
   
   -- Use BETWEEN clause with undefined row.
   -- Does NOT raise NO_DATA_FOUND. Instead:
   --    ORA-22160: element at index [7589] does not exist
   -- But rows before that are inserted. Don't forget about SAVE EXCEPTIONS   
   guide (25) := 1;
   guide (417) := 1; -- Undefined row
   guide (500) := 1;
   guide (5000) := 1;   

   BEGIN
       FORALL indx IN indices of guide BETWEEN guide.FIRST AND LEAST (guide.LAST, 500)
          INSERT INTO favorites
               VALUES family (indx);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE (SQLERRM);
   END;
              
   SELECT COUNT(*) into l_count FROM favorites;           
   DBMS_OUTPUT.PUT_LINE (l_count);    
   
   cleanup;    
   
   -- Use VALUES OF
   guide (-1000) := 1;
   guide (1000) := 5001;
   guide (10000) := 5002;   
   FORALL indx IN VALUES OF guide 
      INSERT INTO favorites
           VALUES family (indx);
           
   SELECT COUNT(*) into l_count FROM favorites;           
   DBMS_OUTPUT.PUT_LINE (l_count);       
   
   cleanup;
   
   -- Use VALUES OF with undefined row
   -- Does NOT raise NO_DATA_FOUND. Instead:
   --    ORA-22160: element at index [7589] does not exist
   -- But rows before that are inserted. Don't forget about SAVE EXCEPTIONS
   
   guide (-1000) := 1;
   guide (1000) := 7589;
   guide (10000) := 5001;   
   BEGIN
       FORALL indx IN VALUES OF guide 
          INSERT INTO favorites
               VALUES family (indx);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.PUT_LINE (SQLERRM);
   END;           
   SELECT COUNT(*) into l_count FROM favorites;           
   DBMS_OUTPUT.PUT_LINE (l_count);       
END;
/



/*======================================================================
| Supplement to the fifth edition of Oracle PL/SQL Programming by Steven
| Feuerstein with Bill Pribyl, Copyright (c) 1997-2009 O'Reilly Media, Inc. 
| To submit corrections or find more code samples visit
| http://oreilly.com/catalog/9780596514464/
*/