Oracle PL/SQL Tutorial/Collections/Table of Number

Accessing an entire Index-by table

SQL> DECLARE
  2    TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable1 num_tab;
  4    myTable2 num_tab;
  5    v_num NUMBER :=13;
  6  BEGIN
  7    myTable1(1):=1001;
  8
  9    myTable1(10):=1002;
 10
 11    myTable1(-10):=1003;
 12
 13    myTable1(v_num):=1004;
 14
 15    myTable2 :=myTable1;
 16
 17    dbms_output.put_line(to_char(myTable2(1))||" "||
 18    to_char(myTable2(10))||" "||
 19    to_char(myTable2(-10))||" "||
 20    to_char(myTable2(v_num)));
 21  END;
 22  /
1001 1002 1003 1004
PL/SQL procedure successfully completed.

Accessing an Index-by table

SQL> DECLARE
  2    TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable num_tab;
  4    v_num NUMBER :=13;
  5  BEGIN
  6    myTable(1):=1001;
  7    myTable(10):=1002;
  8    myTable(-10):=1003;
  9    myTable(v_num):=1004;
 10
 11    dbms_output.put_line(to_char(myTable(1))||" "||
 12    to_char(myTable(10))||" "||
 13    to_char(myTable(-10))||" "||
 14    to_char(myTable(v_num)));
 15  END;
 16  /
1001 1002 1003 1004
PL/SQL procedure successfully completed.

Accessing an undefined row of an Index-by table

SQL> DECLARE
  2    TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable num_tab;
  4    v_num NUMBER :=13;
  5  BEGIN
  6    myTable(1):=1001;
  7    myTable(10):=1002;
  8    myTable(-10):=1003;
  9    myTable(v_num):=1004;
 10    dbms_output.put_line(to_char(myTable(100)));
 11  END;
 12  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 10
<topic title="Sample code using exception while accessing an undefined row of an Index-by table">
 <code><![CDATA[ 
SQL> -- Sample code using exception while accessing an undefined row of an Index-by table
SQL> DECLARE
  2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable numberTabletype;
  4    v_num NUMBER :=13;
  5  BEGIN
  6    myTable(1):=1001;
  7    myTable(10):=1002;
  8    myTable(-10):=1003;
  9    myTable(v_num):=1004;
 10    dbms_output.put_line(to_char(myTable(100)));
 11  EXCEPTION WHEN NO_DATA_FOUND THEN
 12    dbms_output.put_line ("Invalid array element ");
 13  END;
 14  /
Invalid array element
PL/SQL procedure successfully completed.

Accessing Nested Table elements

SQL> declare
  2    Type numberTableType is TABLE OF NUMBER;
  3    v_numarray numberTableType;
  4  begin
  5    v_numarray :=numberTableType(10,20,30,40);
  6
  7
  8    dbms_output.put_line(to_char(v_numarray(1))||", "||to_char(v_numarray(2))||", "||to_char(v_numarray(3))||", "||to_char(v_numarray(4)));
  9
 10
 11    v_numarray(4):=50;
 12    dbms_output.put_line(to_char(v_numarray(1))||", "||to_char(v_numarray(2))||", "||to_char(v_numarray(3))||", "||to_char(v_numarray(4)));
 13  end;
 14  /
10, 20, 30, 40
10, 20, 30, 50
PL/SQL procedure successfully completed.
SQL>

An example of COUNT method

SQL> DECLARE
  2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable numberTabletype;
  4  BEGIN
  5    FOR idx IN 1..10 LOOP
  6      myTable(idx):=(2**idx)+1;
  7    END LOOP;
  8
  9    FOR idx IN 1..myTable.COUNT LOOP
 10      dbms_output.put_line(to_char(myTable(idx)));
 11    END LOOP;
 12
 13  END;
 14  /
3
5
9
17
33
65
129
257
513
1025
PL/SQL procedure successfully completed.

An example of declaring an Index-by table

SQL> DECLARE
  2    TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable num_tab;
  4  BEGIN
  5    /*Do some processing */
  6    null;
  7  END;
  8  /
PL/SQL procedure successfully completed.
SQL>

An example of the DELETE method

SQL> DECLARE
  2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable numberTabletype;
  4  BEGIN
  5    FOR idx IN 1..10 LOOP
  6      myTable(idx):=(2**idx)+1;
  7    END LOOP;
  8
  9    myTable.DELETE(1);
 10
 11    myTable.DELETE(2,5);
 12
 13    myTable.DELETE;
 14  END;
 15  /
PL/SQL procedure successfully completed.

An example of the FIRST, LAST and NEXT methods

SQL> DECLARE
  2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable numberTabletype;
  4    idx BINARY_INTEGER;
  5  BEGIN
  6    FOR idx IN 1..10 LOOP
  7      myTable(idx):=(2**idx)+1;
  8    END LOOP;
  9
 10    idx :=myTable.FIRST;
 11
 12    LOOP
 13      dbms_output.put_line(to_char(myTable(idx)));
 14      EXIT WHEN idx =myTable.LAST;
 15      idx :=myTable.NEXT(idx);
 16    END LOOP;
 17  END;
 18  /
3
5
9
17
33
65
129
257
513
1025
PL/SQL procedure successfully completed.

Assigning rows of an Index-By table by means of a LOOP

SQL> DECLARE
  2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable numberTabletype;
  4  BEGIN
  5    FOR idx IN 1..10 LOOP
  6      myTable(idx):=(2**idx)+1;
  7    END LOOP;
  8
  9    FOR idx IN 1..10 LOOP
 10      dbms_output.put_line(to_char(myTable(idx)));
 11    END LOOP;
 12  END;
 13  /
3
5
9
17
33
65
129
257
513
1025
PL/SQL procedure successfully completed.

Associative arrays

SQL> DECLARE
  2    TYPE site_array IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
  3    v_example_array site_array;
  4  BEGIN
  5    v_example_array("E"):=10;
  6    v_example_array("B"):=11;
  7    v_example_array("S"):=12;
  8    dbms_output.put_line(v_example_array.FIRST);
  9    dbms_output.put_line(v_example_array.LAST);
 10    dbms_output.put_line(TO_CHAR(v_example_array(v_example_array.FIRST)));
 11  END;
 12  /
B
S
11
PL/SQL procedure successfully completed.
SQL>
SQL>

compare two tables of integers

SQL>
SQL> DECLARE
  2     TYPE nested_tab_t IS TABLE OF INTEGER;
  3
  4     tab_1   nested_tab_t := nested_tab_t (1, 2, 3, 4, 5, 6, 7);
  5     tab_2   nested_tab_t := nested_tab_t (7, 6, 5, 4, 3, 2, 1);
  6
  7     PROCEDURE tabs_equal (i_tab_1 IN nested_tab_t, i_tab_2 IN nested_tab_t)
  8     IS
  9        v_equal   BOOLEAN := i_tab_1 = i_tab_2;
 10     BEGIN
 11        IF v_equal IS NULL
 12        THEN
 13           DBMS_OUTPUT.put_line ("null");
 14        ELSIF v_equal
 15        THEN
 16           DBMS_OUTPUT.put_line ("equal");
 17        ELSE
 18           DBMS_OUTPUT.put_line ("not equal");
 19        END IF;
 20     END tabs_equal;
 21  BEGIN
 22     tabs_equal (tab_1, tab_2);
 23     tab_1.EXTEND (1);
 24     tabs_equal (tab_1, tab_2);
 25     tab_2.EXTEND (1);
 26     tabs_equal (tab_1, tab_2);
 27  END;
 28  /
PL/SQL procedure successfully completed.
SQL>

Deleting an Index-by table using an empty Index-by table

SQL> DECLARE
  2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable1 numberTabletype;
  4    myTable2 numberTabletype;
  5    v_num NUMBER :=13;
  6  BEGIN
  7    myTable1(1):=1001;
  8    myTable1(10):=1002;
  9    myTable1(-10):=1003;
 10    myTable1(v_num):=1004;
 11    myTable1:=myTable2;
 12  END;
 13  /
PL/SQL procedure successfully completed.

Initialize table collection with null value

SQL>
SQL> declare
  2    Type numberTableType is TABLE OF NUMBER;
  3    v_numarray2 numberTableType;
  4  begin
  5    v_numarray2 :=numberTableType(NULL);
  6
  7    if v_numarray2 IS NULL then
  8      dbms_output.put_line("v_numarray2 is null");
  9    else
 10      dbms_output.put_line("v_numarray2 is not null");
 11    end if;
 12    if v_numarray2(1) IS NULL then
 13      dbms_output.put_line("The first element of v_numarray2 is null");
 14    end if;
 15  end;
 16  /
v_numarray2 is not null
The first element of v_numarray2 is null
PL/SQL procedure successfully completed.
SQL>
SQL>

Initializing a Nested Table

SQL> DECLARE
  2    TYPE numberTableType IS TABLE OF NUMBER;
  3
  4    v_numarray numberTableType :=numberTableType(10,20,30,40);
  5
  6  BEGIN
  7
  8    dbms_output.put_line("The first element of v_numarray is "||
  9
 10    TO_CHAR(v_numarray(1)));
 11  END;
 12  /
The first element of v_numarray is 10
PL/SQL procedure successfully completed.
SQL>

Is a table collection a null value

SQL>
SQL> declare
  2    Type numberTableType is TABLE OF NUMBER;
  3    v_emptyarray numberTableType :=numberTableType();
  4  begin
  5    if v_emptyarray IS NULL then
  6      dbms_output.put_line("v_emptyarray is null");
  7    else
  8      dbms_output.put_line("v_emptyarray is NOT null");
  9    end if;
 10  end;
 11  /
v_emptyarray is NOT null
PL/SQL procedure successfully completed.
SQL>

Loop through table of number by index

SQL>
SQL>
SQL> create table employee
  2          (
  3           emp_no                 integer     primary key
  4          ,lastname               varchar2(20)    not null
  5          ,firstname              varchar2(15)    not null
  6          ,midinit                varchar2(1)
  7          ,street                 varchar2(30)
  8          ,city                   varchar2(20)
  9          ,state                  varchar2(2)
 10          ,zip                    varchar2(5)
 11          ,zip_4                  varchar2(4)
 12          ,area_code              varchar2(3)
 13          ,phone                  varchar2(8)
 14          ,salary                 number(5,2)
 15          ,birthdate              date
 16          ,hiredate               date
 17          ,title                  varchar2(20)
 18          ,dept_no                integer
 19        ,mgr              integer
 20        ,region           number
 21        ,division         number
 22        ,total_sales          number
 23         );
Table created.
SQL>
SQL>
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, birthdate, title)
  2  values (1,"Gardinia","Joy","R","688 Ave","New York","NY","12122","2333","212","200-3393","12-nov-1956","President");
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values (2,"Anderson","Lucy","J","33 Ave","New York","NY","43552","6633","212","234-4444",7.75,"21-mar-1951","1-feb-1994","Sales Manager",2,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2  values (3,"Somers","Ingrid","E","12 Ave","New York","NY","76822","8763","212","867-6893",7.75,"14-feb-1963","15-mar-1995","Sales Clerk",2,2,100,10,10000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values (4,"Washington","Georgia","J","13th Street","New York","NY","43122","4333","212","340-4365",11.50,"2-jul-1963","21-apr-1994","Designer",1,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values (5,"Doright","Dudley","J","56 Langer Street","Staten Island","NY","23332","4983","718","777-4365",21.65,"15-may-1958","2-aug-1994","Designer",1,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2  values ( 6,"Doright","Dorothy","R","56 Langer Street","Staten Island","NY","23332","4983","718","777-4365",24.65,"10-dec-1968","2-aug-1994","Designer",1,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 7,"Perry","Donna","R","1st Ave","New York","NY","44444","3444","212","111-6893",7.75,"14-feb-1967","15-mar-1995","Sales Clerk",2,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 8,"Roger","John","E","67 H Ave","New York","NY","33822","1163","212","122-6893",10.00,"14-jun-1956","15-mar-1995","Accountant",3,1,100,10,40000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 9,"Hall","Ted","R","1236 Lane","New York","NY","33823","1164","212","222-4393",13.00,"10-jun-1959","15-aug-1997","Sales Representative",3,1,100,10,50000);
1 row created.
SQL>
SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip, zip_4, area_code, phone, salary, birthdate, hiredate, title, dept_no, mgr, region, division, total_sales)
  2  values ( 10,"Barbee","Candice","L","400 Street","New York","NY","33811","2009","212","321-6873",12.00,"10-oct-1964","15-jan-1999","Sales Representative",3,1,100,10,35000);
1 row created.
SQL>
SQL>
SQL>
SQL> declare
  2    type num_type is    table of number;
  3    l_manager_array     num_type := num_type(1,2,3);
  4    child_record_found  exception;
  5    pragma    exception_init(child_record_found, -2292);
  6    v_index             number;
  7  begin
  8    forall i in l_manager_array.FIRST..l_manager_array.LAST
  9       delete from employee WHERE mgr = l_manager_array(i);
 10       dbms_output.put_line(sql%rowcount);
 11    exception
 12       when child_record_found then
 13           v_index := l_manager_array.first;
 14           dbms_output.put_line("child record found");
 15           for i in l_manager_array.FIRST..l_manager_array.LAST
 16           loop
 17             dbms_output.put_line("delete for manager "
 18                                  ||l_manager_array(v_index)
 19                                  ||" deleted "
 20                                  ||SQL%BULK_ROWCOUNT(v_index)
 21                                  ||" rows.");
 22             v_index := l_manager_Array.NEXT(v_index);
 23           end loop;
 24           commit;
 25  end;
 26  /
9
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>

NULL and Empty Nested Tables and NULL elements

SQL> declare
  2
  3    Type numberTableType is TABLE OF NUMBER;
  4
  5    v_numarray numberTableType;
  6
  7  begin
  8
  9    if v_numarray IS NULL then
 10
 11      dbms_output.put_line("v_numarray is null");
 12
 13    end if;
 14
 15    v_numarray :=NULL;
 16
 17    if v_numarray IS NULL then
 18
 19      dbms_output.put_line("v_numarray is null after assignment");
 20
 21    end if;
 22  end;
 23  /
v_numarray is null
v_numarray is null after assignment
PL/SQL procedure successfully completed.
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>

ORA-06531: Reference to uninitialized collection

SQL>
SQL> declare
  2    Type numberTableType is TABLE OF NUMBER;
  3    v_numarray1 numberTableType;
  4  begin
  5    if v_numarray1 IS NULL then
  6      dbms_output.put_line("v_numarray1 is null");
  7    end if;
  8    if v_numarray1(1) IS NULL then
  9      dbms_output.put_line("The first element of v_numarray1 is null");
 10    end if;
 11  end;
 12  /
v_numarray1 is null
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 8

SQL>

ORA-06533: Subscript beyond count

SQL>
SQL>
SQL> declare
  2    Type numberTableType is TABLE OF NUMBER;
  3
  4    v_numarray numberTableType;
  5
  6  begin
  7
  8    v_numarray :=numberTableType(10,20,30,40);
  9
 10    dbms_output.put_line(to_char(v_numarray(1))||", "||to_char(v_numarray(2))||", "||to_char(v_numarray(3))||", "||to_char(v_numarray(4)));
 11
 12    dbms_output.put_line(to_char(v_numarray(5)));
 13  end;
 14  /
10, 20, 30, 40
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 12

SQL>
SQL>

Using the EXISTS method

SQL> DECLARE
  2    TYPE numberTabletype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    myTable numberTabletype;
  4    v_num NUMBER :=13;
  5  BEGIN
  6    myTable(1):=1001;
  7
  8    myTable(10):=1002;
  9
 10    myTable(-10):=1003;
 11
 12    myTable(v_num):=1004;
 13
 14    IF myTable.EXISTS(100) THEN
 15      dbms_output.put_line(to_char(myTable(100)));
 16    END IF;
 17
 18  END;
 19  /
PL/SQL procedure successfully completed.