Use VALUES OF with undefined row : forall « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. forall

SQL> CREATE TABLE favorites (
  2     flavor VARCHAR2(100),
  3     NAME VARCHAR2(100));

Table created.

SQL>
SQL> DECLARE
  2     TYPE favorites_tt IS TABLE OF favorites%ROWTYPE INDEX BY PLS_INTEGER;
  3     TYPE guide_tt IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
  4
  5     family   favorites_tt;
  6     guide    guide_tt;
  7     l_count INTEGER;
  8
  9     PROCEDURE cleanup IS
 10     BEGIN
 11        DELETE FROM favorites;
 12        guide.DELETE;
 13     END;
 14  BEGIN
 15     family (1).flavor := 'CHOCOLATE';
 16     family (1).NAME := 'VEVA';
 17     family (25).flavor := 'STRAWBERRY';
 18     family (25).NAME := 'STEVEN';
 19     family (500).flavor := 'VANILLA';
 20     family (500).NAME := 'CHRIS';
 21     family (5000).flavor := 'ROCKY ROAD';
 22     family (5000).NAME := 'ELI';
 23     family (5001).flavor := 'PINEAPPLE';
 24     family (5001).NAME := 'MOSHE';
 25     family (5002).flavor := 'EVERYTHING';
 26     family (5002).NAME := 'MICA';
 27
 28
 29     guide (-1000) := 1;
 30     guide (1000) := 7589;
 31     guide (10000) := 5001;
 32     BEGIN
 33         FORALL indx IN VALUES OF guide INSERT INTO favorites VALUES family (indx);
 34     EXCEPTION
 35        WHEN OTHERS
 36        THEN
 37           DBMS_OUTPUT.PUT_LINE (SQLERRM);
 38     END;
 39     SELECT COUNT(*) into l_count FROM favorites;
 40     DBMS_OUTPUT.PUT_LINE (l_count);
 41
 42  END;
 43  /

PL/SQL procedure successfully completed.

SQL>
SQL> DROP TABLE favorites;

Table dropped.

SQL>
SQL>