Oracle PL/SQL/PL SQL/Table of number

A nested table of a scalar variable:

   
SQL>
SQL> DECLARE
  2    TYPE number_table IS TABLE OF NUMBER;
  3    list NUMBER_TABLE := number_table(1,2,3,4,5,6,7,8);
  4  BEGIN
  5    list.DELETE(2);
  6    FOR i IN 1..list.COUNT LOOP
  7      IF list.EXISTS(i) THEN
  8        dbms_output.put("["||list(i)||"]");
  9      END IF;
 10    END LOOP;
 11    dbms_output.new_line;
 12  END;
 13  /
[1][3][4][5][6][7]
PL/SQL procedure successfully completed.
SQL>

assignments to nested table elements, and the ORA-6533 error.

    
SQL>
SQL> DECLARE
  2    TYPE NumbersTab IS TABLE OF NUMBER;
  3    v_Numbers NumbersTab := NumbersTab(1, 2, 3);
  4  BEGIN
  5    -- v_Numbers was initialized to have 3 elements. So the
  6    -- following assignments are all legal.
  7    v_Numbers(1) := 7;
  8    v_Numbers(2) := -1;
  9
 10    -- However, this assignment will raise ORA-6533.
 11    v_Numbers(4) := 4;
 12  END;
 13  /
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 11

SQL>
SQL>

Associate array: varchar2 to number map

   
SQL> DECLARE
  2    TYPE varcharType IS TABLE OF NUMBER(17,2) INDEX BY VARCHAR2(12);
  3    city               varcharType;
  4    sales_figure       NUMBER;
  5    first              VARCHAR2(12);
  6    last               VARCHAR2(12);
  7  BEGIN
  8    city("West") := 2.3;
  9    city("East") := 1.9;
 10    city("MidWest") := 3.4;
 11    sales_figure := city("East");
 12    first := city.FIRST;
 13    last := city.LAST;
 14    sales_figure := city(city.LAST);
 15    city("West") := 1.55;
 16    DBMS_OUTPUT.PUT_LINE (first);
 17    DBMS_OUTPUT.PUT_LINE (last);
 18    DBMS_OUTPUT.PUT_LINE (sales_figure);
 19  END;
 20  /
East
West
2.3
PL/SQL procedure successfully completed.
SQL>

Clear the salaries table by assigning the empty version to it

    
SQL>
SQL> SET ECHO ON
SQL>
SQL> DECLARE
  2      TYPE salary_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3
  4      salaries salary_table;
  5      salaries_empty salary_table;
  6  BEGIN
  7      salaries(20) := 50550;
  8      salaries(40) := 50550;
  9      salaries(60) := 50550;
 10
 11      salaries := salaries_empty;
 12  END;
 13  /
PL/SQL procedure successfully completed.
SQL>
SQL> --

Declare an index-by table variable to hold the employee records that we read in

    
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2      TYPE num_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3
  4      nums num_table;
  5      some_num NUMBER;
  6  BEGIN
  7      nums(10) := 11;
  8
  9      BEGIN
 10          some_num := nums(11);
 11      EXCEPTION
 12      WHEN NO_DATA_FOUND THEN
 13          DBMS_OUTPUT.PUT_LINE("Element 11 does not exist.");
 14      END;
 15
 16      IF nums.EXISTS(11) THEN
 17          some_num := nums(11);
 18      ELSE
 19          DBMS_OUTPUT.PUT_LINE("Element 11 still does not exist.");
 20      END IF;
 21  END;
 22  /
Element 11 does not exist.
Element 11 still does not exist.
PL/SQL procedure successfully completed.
SQL>
SQL> --

Delete a elements from 2, 3 and 4.

   
SQL>
SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
  2  /
Type created.
SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
  2   BEGIN
  3       FOR i IN list_in.FIRST..list_in.LAST LOOP
  4         IF list_in.EXISTS(i) THEN
  5           DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
  6         END IF;
  7       END LOOP;
  8   END print_list;
  9   /
Procedure created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2     number_list NUMBER_TABLE;
  3
  4   BEGIN
  5     IF NOT number_list.EXISTS(1) THEN
  6       number_list := number_table(1,2,3,4,5);
  7     END IF;
  8
  9     DBMS_OUTPUT.PUT_LINE("Nested table before a deletion");
 10     print_list(number_list);
 11
 12     number_list.DELETE(2,4);
 13
 14     DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
 15     print_list(number_list);
 16   END;
 17   /
Nested table before a deletion
List 1
List 2
List 3
List 4
List 5
Nested table after a deletion
List 1
List 5
PL/SQL procedure successfully completed.
SQL>

Delete element 2.

   
SQL>
SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
  2  /
Type created.
SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
  2   BEGIN
  3       FOR i IN list_in.FIRST..list_in.LAST LOOP
  4         IF list_in.EXISTS(i) THEN
  5           DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
  6         END IF;
  7       END LOOP;
  8   END print_list;
  9   /
Procedure created.
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
  2     number_list NUMBER_TABLE;
  3   BEGIN
  4     IF NOT number_list.EXISTS(1) THEN
  5       number_list := number_table(1,2,3,4,5);
  6     END IF;
  7
  8     DBMS_OUTPUT.PUT_LINE("Nested table before a deletion");
  9     print_list(number_list);
 10
 11     number_list.DELETE(2);
 12
 13      
 14     DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
 15     print_list(number_list);
 16   END;
 17   /
Nested table before a deletion
List 1
List 2
List 3
List 4
List 5
Nested table after a deletion
List 1
List 3
List 4
List 5
PL/SQL procedure successfully completed.
SQL>
SQL>

EXISTS method

    

SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
  2  /
Type created.
SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
  2   BEGIN
  3       FOR i IN list_in.FIRST..list_in.LAST LOOP
  4         IF list_in.EXISTS(i) THEN
  5           DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
  6         END IF;
  7       END LOOP;
  8   END print_list;
  9   /
Procedure created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2     number_list NUMBER_TABLE;
  3
  4   BEGIN
  5     IF NOT number_list.EXISTS(1) THEN
  6       number_list := number_table(1,2,3,4,5);
  7     END IF;
  8
  9     DBMS_OUTPUT.PUT_LINE("Nested table before a deletion");
 10     print_list(number_list);
 11
 12     number_list.DELETE(2);
 13
 14     DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
 15     print_list(number_list);
 16   END;
 17   /
Nested table before a deletion
List 1
List 2
List 3
List 4
List 5
Nested table after a deletion
List 1
List 3
List 4
List 5
PL/SQL procedure successfully completed.
SQL>

Extend space in number list.

    

SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL>
SQL> -- Create a table for the example.
SQL> CREATE TABLE myTable
  2  (id                NUMBER              NOT NULL
  3  ,CONSTRAINT id_pk  PRIMARY KEY (id));
Table created.
SQL>
SQL>
SQL> 
SQL> DECLARE
  2    TYPE number_table IS TABLE OF myTable.id%TYPE;
  3
  4    number_list NUMBER_TABLE := number_table();
  5
  6  BEGIN
  7
  8    
  9    number_list.EXTEND(10000);
 10
 11    
 12    FOR i IN 1..10000 LOOP
 13
 14      
 15      number_list(i) := i;
 16
 17    END LOOP;
 18
 19    
 20    FORALL i IN 1..number_list.COUNT
 21      INSERT INTO myTable VALUES (number_list(i));
 22  END;
 23  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myTable;
Table dropped.

FIRST method returns the lowest subscript value used in a collection

    
SQL>
SQL> DECLARE
  2    TYPE number_table IS TABLE OF INTEGER INDEX BY VARCHAR2(9 CHAR);
  3    number_list NUMBER_TABLE;
  4  BEGIN
  5    
  6    number_list("One") := 1;
  7    number_list("Two") := 2;
  8    number_list("Nine") := 9;
  9    
 10    DBMS_OUTPUT.PUT_LINE("FIRST Index "||number_list.FIRST);
 11  END;
 12  /
FIRST Index Nine
PL/SQL procedure successfully completed.

Legal and illegal table assignments.

    
SQL>
SQL> DECLARE
  2    TYPE NumbersTab IS TABLE OF NUMBER;
  3    v_Numbers NumbersTab := NumbersTab(1, 2, 3);
  4  BEGIN
  5    v_Numbers(1) := 7;
  6    v_Numbers(2) := -1;
  7
  8    v_Numbers(4) := 4;
  9  END;
 10  /
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 8

SQL>
SQL>

NULL key value in an index-by table

    
SQL>
SQL> DECLARE
  2    TYPE t_NumTab IS TABLE OF NUMBER
  3      INDEX BY BINARY_INTEGER;
  4    v_TempVAR t_NumTab;
  5  BEGIN
  6    v_TempVAR(NULL) := 4;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 6

SQL>

number_list.EXTEND(2): Add two null value members at the end of the list.

   
 
 
SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
  2  /
Type created.
SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
  2   BEGIN
  3       FOR i IN list_in.FIRST..list_in.LAST LOOP
  4         IF list_in.EXISTS(i) THEN
  5           DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
  6         END IF;
  7       END LOOP;
  8   END print_list;
  9   /
Procedure created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2     number_list NUMBER_TABLE;
  3
  4   BEGIN
  5     IF NOT number_list.EXISTS(1) THEN
  6       number_list := number_table(1,2,3,4,5);
  7     END IF;
  8
  9     print_list(number_list);
 10
 11     -- Add two null value members at the end of the list.
 12     number_list.EXTEND(2);
 13
 14     -- Print revised contents.
 15     DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
 16     print_list(number_list);
 17   END;
 18   /
List 1
List 2
List 3
List 4
List 5
Nested table after a deletion
List 1
List 2
List 3
List 4
List 5
List
List
PL/SQL procedure successfully completed.
SQL>
SQL>

number_list.EXTEND(3,4): Add three members at the end of the list and copy the contents of item 4

   
 
SQL> create or replace TYPE number_table IS TABLE OF INTEGER;
  2  /
Type created.
SQL> create or replace PROCEDURE print_list(list_in NUMBER_TABLE) IS
  2   BEGIN
  3       FOR i IN list_in.FIRST..list_in.LAST LOOP
  4         IF list_in.EXISTS(i) THEN
  5           DBMS_OUTPUT.PUT_LINE("List "||list_in(i));
  6         END IF;
  7       END LOOP;
  8   END print_list;
  9   /
Procedure created.
SQL>
SQL>
SQL>
SQL> DECLARE
  2     number_list NUMBER_TABLE;
  3
  4   BEGIN
  5     IF NOT number_list.EXISTS(1) THEN
  6       number_list := number_table(1,2,3,4,5);
  7     END IF;
  8
  9     print_list(number_list);
 10
 11     
 12     number_list.EXTEND(3,4);
 13
 14     
 15     DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
 16     print_list(number_list);
 17   END;
 18   /
List 1
List 2
List 3
List 4
List 5
Nested table after a deletion
List 1
List 2
List 3
List 4
List 5
List 4
List 4
List 4
PL/SQL procedure successfully completed.
SQL>

Number Table by BINARY_INTEGER

    
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    TYPE NumberTab IS TABLE OF NUMBER
  3      INDEX BY BINARY_INTEGER;
  4    v_Numbers NumberTab;
  5  BEGIN
  6    FOR v_Count IN 1..10 LOOP
  7      v_Numbers(v_Count) := v_Count * 10;
  8    END LOOP;
  9
 10    DBMS_OUTPUT.PUT_LINE("Table elements: ");
 11    FOR v_Count IN 1..10 LOOP
 12      DBMS_OUTPUT.PUT_LINE("  v_Numbers(" || v_Count || "): " ||
 13                           v_Numbers(v_Count));
 14    END LOOP;
 15
 16    BEGIN
 17      DBMS_OUTPUT.PUT_LINE("v_Numbers(11): " || v_Numbers(11));
 18    EXCEPTION
 19      WHEN NO_DATA_FOUND THEN
 20         DBMS_OUTPUT.PUT_LINE(
 21           "No data found reading v_Numbers(11)!");
 22    END;
 23  END;
 24  /
Table elements:
v_Numbers(1): 10
v_Numbers(2): 20
v_Numbers(3): 30
v_Numbers(4): 40
v_Numbers(5): 50
v_Numbers(6): 60
v_Numbers(7): 70
v_Numbers(8): 80
v_Numbers(9): 90
v_Numbers(10): 100
No data found reading v_Numbers(11)!
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Table of number index by varchar2

    
SQL> DECLARE
  2    TYPE charTableType IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  3    charTable charTableType;
  4    stringTable charTableType;
  5    howmany NUMBER;
  6    which VARCHAR2(64);
  7
  8  BEGIN
  9    charTable("A") := 1;
 10    charTable("B") := 2;
 11    howmany := charTable("A");
 12
 13    stringTable("C") := 3;
 14    stringTable("D") := 1000;
 15    stringTable("D") := 1001;
 16    which := stringTable.FIRST;
 17    dbms_output.put_line(which);
 18    which := stringTable.LAST;
 19    dbms_output.put_line(which);
 20    howmany := stringTable(stringTable.LAST);
 21    dbms_output.put_line(howmany);
 22  END;
 23  /
C
D
1001
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Try to insert elements 3 through 5

    
SQL>
SQL> CREATE TABLE MyTable (
  2    num_col    NUMBER,
  3    char_col   VARCHAR2(60)
  4    );
Table created.
SQL>
SQL>
SQL> DECLARE
  2    TYPE t_Numbers IS TABLE OF NUMBER;
  3    v_Numbers t_Numbers := t_Numbers(1, 2, 3, 4, 5);
  4  BEGIN
  5    v_Numbers.DELETE(4);
  6
  7    FORALL v_Count IN 3..5
  8      INSERT INTO MyTable (num_col) VALUES (v_Numbers(v_Count));
  9  END;
 10  /
DECLARE
*
ERROR at line 1:
ORA-22160: element at index [4] does not exist
ORA-06512: at line 7

SQL>
SQL> select * from MyTable;
no rows selected
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>

TYPE NumbersTab IS TABLE OF NUMBER.

    
SQL>
SQL> DECLARE
  2    TYPE NumbersTab IS TABLE OF NUMBER;
  3
  4    v_Tab1 NumbersTab := NumbersTab(-1);
  5
  6    v_Primes NumbersTab := NumbersTab(1, 2, 3, 5, 7);
  7
  8    v_Tab2 NumbersTab := NumbersTab();
  9  BEGIN
 10    v_Tab1(1) := 12345;
 11
 12    FOR v_Count IN 1..5 LOOP
 13      DBMS_OUTPUT.PUT(v_Primes(v_Count) || " ");
 14    END LOOP;
 15    DBMS_OUTPUT.NEW_LINE;
 16  END;
 17  /
1 2 3 5 7
PL/SQL procedure successfully completed.
SQL>
SQL>

Use nested table constructors.

    
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    TYPE NumbersTab IS TABLE OF NUMBER;
  3
  4    
  5    v_Tab1 NumbersTab := NumbersTab(-1);
  6
  7    
  8    v_Primes NumbersTab := NumbersTab(1, 2, 3, 5, 7);
  9
 10    
 11    v_Tab2 NumbersTab := NumbersTab();
 12  BEGIN
 13    
 14    
 15    v_Tab1(1) := 12345;
 16
 17    
 18    FOR v_Count IN 1..5 LOOP
 19      DBMS_OUTPUT.PUT(v_Primes(v_Count) || " ");
 20    END LOOP;
 21    DBMS_OUTPUT.NEW_LINE;
 22  END;
 23  /
1 2 3 5 7
PL/SQL procedure successfully completed.
SQL>
SQL>

Use the Oracle10g Collection API COUNT method against an element.

   
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> DECLARE
  2
  3    TYPE number_table IS TABLE OF INTEGER;
  4
  5    
  6    number_list NUMBER_TABLE := number_table(1,2,3,4,5);
  7
  8  BEGIN
  9
 10
 11    
 12    DBMS_OUTPUT.PUT_LINE("Count :"||number_list.COUNT);
 13
 14  END;
 15  /
Count :5
PL/SQL procedure successfully completed.
SQL>

Use the Oracle10g Collection API DELETE method against a set of elements.

   
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> DECLARE
  2
  3    
  4    TYPE number_table IS TABLE OF INTEGER;
  5
  6    
  7    number_list NUMBER_TABLE;
  8
  9    
 10    PROCEDURE print_list (list_in NUMBER_TABLE) IS
 11
 12    BEGIN
 13
 14      
 15      FOR i IN list_in.FIRST..list_in.LAST LOOP
 16
 17        
 18        IF list_in.EXISTS(i) THEN
 19          DBMS_OUTPUT.PUT_LINE("List:"||list_in(i));
 20
 21        END IF;
 22
 23      END LOOP;
 24
 25    END print_list;
 26
 27  BEGIN
 28
 29    
 30    IF NOT number_list.EXISTS(1) THEN
 31
 32      
 33      number_list := number_table(1,2,3,4,5);
 34
 35    END IF;
 36
 37    print_list(number_list);
 38
 39    
 40    number_list.DELETE(2,4);
 41
 42
 43    print_list(number_list);
 44
 45  END;
 46  /
List:1
List:2
List:3
List:4
List:5
List:1
List:5
PL/SQL procedure successfully completed.
SQL>

Use the Oracle10g Collection API EXISTS method against an element.

   
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> DECLARE
  2
  3    
  4    TYPE number_table IS TABLE OF INTEGER;
  5
  6    
  7    number_list NUMBER_TABLE;
  8
  9    
 10    PROCEDURE print_list
 11      (list_in NUMBER_TABLE) IS
 12
 13    BEGIN
 14
 15      
 16      FOR i IN list_in.FIRST..list_in.LAST LOOP
 17
 18        
 19        IF list_in.EXISTS(i) THEN
 20
 21          
 22          DBMS_OUTPUT.PUT_LINE("List :"||list_in(i));
 23
 24        END IF;
 25
 26      END LOOP;
 27
 28    END print_list;
 29
 30  BEGIN
 31
 32    
 33    IF NOT number_list.EXISTS(1) THEN
 34
 35      
 36      number_list := number_table(1,2,3,4,5);
 37
 38    END IF;
 39
 40    
 41    DBMS_OUTPUT.PUT_LINE("Nested table before a deletion");
 42
 43    
 44    print_list(number_list);
 45
 46    
 47    number_list.DELETE(2);
 48
 49    
 50    DBMS_OUTPUT.PUT_LINE(CHR(10)||"Nested table after a deletion");
 51
 52    
 53    print_list(number_list);
 54
 55  END;
 56  /
Nested table before a deletion
List :1
List :2
List :3
List :4
List :5
Nested table after a deletion
List :1
List :3
List :4
List :5
PL/SQL procedure successfully completed.
SQL>

Use the Oracle10g Collection API EXTEND method against an element.

   
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> DECLARE
  2
  3    
  4    TYPE number_table IS TABLE OF INTEGER;
  5
  6    
  7    number_list NUMBER_TABLE := number_table(1,2);
  8
  9    
 10    PROCEDURE print_list
 11      (list_in NUMBER_TABLE) IS
 12
 13    BEGIN
 14
 15      
 16      FOR i IN list_in.FIRST..list_in.LAST LOOP
 17
 18        
 19        IF list_in.EXISTS(i) THEN
 20
 21          
 22          DBMS_OUTPUT.PUT_LINE("List :"||list_in(i));
 23
 24        END IF;
 25
 26      END LOOP;
 27
 28    END print_list;
 29
 30  BEGIN
 31    print_list(number_list);
 32
 33    
 34    number_list.EXTEND(2);
 35
 36    
 37    number_list.EXTEND(3,2);
 38
 39    print_list(number_list);
 40
 41  END;
 42  /
List :1
List :2
List :1
List :2
List :
List :
List :2
List :2
List :2
PL/SQL procedure successfully completed.
SQL>

Use the Oracle10g Collection API FIRST and LAST methods against a collection.

   
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> DECLARE
  2
  3    
  4    TYPE number_table IS TABLE OF INTEGER INDEX BY VARCHAR2(9 CHAR);
  5
  6    
  7    number_list NUMBER_TABLE;
  8
  9  BEGIN
 10
 11    
 12    number_list("One") := 1;
 13    number_list("Two") := 2;
 14    number_list("Nine") := 9;
 15
 16    
 17    DBMS_OUTPUT.PUT_LINE("FIRST Index :"||number_list.FIRST);
 18    DBMS_OUTPUT.PUT_LINE("NEXT  Index :"||number_list.NEXT(number_list.FIRST));
 19
 20    
 21    DBMS_OUTPUT.PUT_LINE(CHR(10)||"LAST  Index :"||number_list.LAST);
 22    DBMS_OUTPUT.PUT_LINE(" PRIOR Index :"||number_list.PRIOR(number_list.LAST));
 23
 24  END;
 25  /
FIRST Index :Nine
NEXT  Index :One
LAST  Index :Two
PRIOR Index :One
PL/SQL procedure successfully completed.

Use variable.Last to get the last element

    
SQL> DECLARE
  2    TYPE charTableType IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  3    charTable charTableType;
  4    stringTable charTableType;
  5    howmany NUMBER;
  6    which VARCHAR2(64);
  7
  8  BEGIN
  9    charTable("A") := 1;
 10    charTable("B") := 2;
 11    howmany := charTable("A");
 12
 13    stringTable("C") := 3;
 14    stringTable("D") := 1000;
 15    stringTable("D") := 1001;
 16    which := stringTable.FIRST;
 17
 18    dbms_output.put_line(which);
 19    which := stringTable.LAST;
 20    dbms_output.put_line(which);
 21    howmany := stringTable(stringTable.LAST);
 22
 23    dbms_output.put_line(howmany);
 24  END;
 25  /
C
D
1001
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>

use virtual table in PL/SQL block

    
SQL>
SQL>
SQL> create or replace type virtual_table_type as table of number
  2      /
Type created.
SQL> create or replace
  2      function virtual_table( p_start number,
  3                              p_end number ) return virtual_table_type as
  4        l_vt_type virtual_table_type := virtual_table_type();
  5      begin
  6        for i in p_start .. p_end loop
  7          l_vt_type.extend();
  8          dbms_output.put_line( "adding " || i || " to collection..." );
  9          l_vt_type(l_vt_type.count) := i;
 10       end loop;
 11       dbms_output.put_line( "done..." );
 12       return l_vt_type;
 13     end virtual_table;
 14     /
Function created.
SQL> set serveroutput on
SQL>      begin
  2        for x in ( select *
  3                     from table( virtual_table( -2, 2) ) )
  4        loop
  5          dbms_output.put_line( "printing from anonymous block " ||
  6                                x.column_value );
  7        end loop;
  8      end;
  9      /
adding -2 to collection...
adding -1 to collection...
adding 0 to collection...
adding 1 to collection...
adding 2 to collection...
done...
printing from anonymous block -2
printing from anonymous block -1
printing from anonymous block 0
printing from anonymous block 1
printing from anonymous block 2
PL/SQL procedure successfully completed.