Oracle PL/SQL/PL SQL/Table of Varchar2

Allocate space as you increment the index.

    
SQL>
SQL> DECLARE
  2    
  3    TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR);
  4
  5    
  6    charArray charArrayType := charArrayType();
  7  BEGIN
  8    FOR i IN 1..3 LOOP
  9      charArray.EXTEND;
 10      IF    i = 1 THEN
 11        charArray(i) := "A";
 12      ELSIF i = 2 THEN
 13        charArray(i) := "B";
 14      ELSIF i = 3 THEN
 15        charArray(i) := "C";
 16      END IF;
 17    END LOOP;
 18
 19    FOR i IN 1..3 LOOP
 20      dbms_output.put     (i||":");
 21      dbms_output.put_line(charArray(i));
 22    END LOOP;
 23  END;
 24  /
1:A
2:B
3:C
PL/SQL procedure successfully completed.

A NULL table, and a table with a NULL element.

     
SQL>
SQL> DECLARE
  2    TYPE wordTableType IS TABLE OF VARCHAR2(50);
  3
  4    myTable1 wordTableType;
  5
  6    myTable2 wordTableType := wordTableType();
  7  BEGIN
  8    IF myTable1 IS NULL THEN
  9      DBMS_OUTPUT.PUT_LINE("myTable1 is NULL");
 10    ELSE
 11      DBMS_OUTPUT.PUT_LINE("myTable1 is not NULL");
 12    END IF;
 13
 14    IF myTable2 IS NULL THEN
 15      DBMS_OUTPUT.PUT_LINE("myTable2 is NULL");
 16    ELSE
 17      DBMS_OUTPUT.PUT_LINE("myTable2 is not NULL");
 18    END IF;
 19  END;
 20  /
myTable1 is NULL
myTable2 is not NULL
PL/SQL procedure successfully completed.
SQL>
SQL>

Assign the character index to a variable.

    
SQL>
SQL> DECLARE
  2    current VARCHAR2(9 CHAR);
  3    element INTEGER;
  4
  5    TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
  6    TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY VARCHAR2(9 CHAR);
  7
  8    month MONTHS_VARRAY := months_varray("January","February","March","April","May","June");
  9
 10    calendar CALENDAR_TABLE;
 11  BEGIN
 12    IF calendar.COUNT = 0 THEN
 13      FOR i IN month.FIRST..month.LAST LOOP
 14        calendar(month(i)) := TO_CHAR(i);
 15        DBMS_OUTPUT.PUT_LINE("Index ["||month(i)||"] is ["||i||"]");
 16      END LOOP;
 17
 18
 19    END IF;
 20  END;
 21  /
Index [January] is [1]
Index [February] is [2]
Index [March] is [3]
Index [April] is [4]
Index [May] is [5]
Index [June] is [6]
PL/SQL procedure successfully completed.

Assign the first character index to a variable.

    
SQL>
SQL> DECLARE
  2    current VARCHAR2(9 CHAR);
  3    element INTEGER;
  4
  5    TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);
  6    TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR) INDEX BY VARCHAR2(9 CHAR);
  7
  8    month MONTHS_VARRAY := months_varray("January","February","March","April","May","June","July","August","September","October","November","December");
  9
 10    calendar CALENDAR_TABLE;
 11  BEGIN
 12    IF calendar.COUNT = 0 THEN
 13      FOR i IN month.FIRST..month.LAST LOOP
 14        calendar(month(i)) := TO_CHAR(i);
 15        DBMS_OUTPUT.PUT_LINE("Index ["||month(i)||"] is ["||i||"]");
 16      END LOOP;
 17
 18
 19    END IF;
 20  END;
 21  /
Index [January] is [1]
Index [February] is [2]
Index [March] is [3]
Index [April] is [4]
Index [May] is [5]
Index [June] is [6]
Index [July] is [7]
Index [August] is [8]
Index [September] is [9]
Index [October] is [10]
Index [November] is [11]
Index [December] is [12]
PL/SQL procedure successfully completed.

Assign to three elements of the table. Note that the key values are not sequential

     
SQL>
SQL> DECLARE
  2    TYPE CharacterTab IS TABLE OF VARCHAR2(10)
  3      INDEX BY BINARY_INTEGER;
  4    v_Characters CharacterTab;
  5  BEGIN
  6    v_Characters(0) := "H";
  7    v_Characters(-7) := "S";
  8    v_Characters(3) := "S";
  9  END;
 10  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

Assign values to subscripted members of the nested table.

    
SQL>
SQL> DECLARE
  2    TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR);
  3    charArray charArrayType := charArrayType(NULL,NULL,NULL);
  4  BEGIN
  5
  6    charArray(1) := "A";
  7    charArray(2) := "B";
  8    charArray(3) := "C";
  9
 10    FOR i IN 1..3 LOOP
 11      dbms_output.put_line(i||":"||charArray(i));
 12    END LOOP;
 13  END;
 14  /
1:A
2:B
3:C
PL/SQL procedure successfully completed.
SQL>

Assign value to table record after delete method call

     
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200)
  3        index by binary_integer;
  4
  5      myText_table     myTextTableType;
  6      l_empty_table    myTextTableType;
  7    begin
  8      myText_table(10) := "A";
  9      myText_table(20) := "AA";
 10      myText_table(30) := "AAA";
 11
 12      dbms_output.put_line(myText_table.count);
 13
 14      myText_table.DELETE;
 15      dbms_output.put_line(myText_table.count);
 16
 17      myText_table(15) := "some text";
 18      myText_table(25) := "some more text";
 19      dbms_output.put_line(myText_table.count);
 20    end;
 21    /
3
0
2
PL/SQL procedure successfully completed.
SQL>
SQL>

Assign value to table records

     
SQL>
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200)
  3        index by binary_integer;
  4
  5      myText_table     myTextTableType;
  6      l_empty_table    myTextTableType;
  7    begin
  8      myText_table(10) := "A";
  9      myText_table(20) := "AA";
 10      myText_table(30) := "AAA";
 11
 12    end;
 13    /
PL/SQL procedure successfully completed.
SQL>

Assign value to varchar2 collection by index

    
SQL>
SQL> DECLARE
  2    TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR);
  3
  4    charArray charArrayType := charArrayType(NULL,NULL,NULL);
  5  BEGIN
  6
  7    FOR i IN 1..3 LOOP
  8      dbms_output.put     ("charArray Varray:"||i);
  9      dbms_output.put_line(":"||charArray(i));
 10    END LOOP;
 11
 12    charArray(1) := "A";
 13    charArray(2) := "B";
 14    charArray(3) := "C";
 15
 16    FOR i IN 1..3 LOOP
 17      dbms_output.put_line(i||":"||charArray(i));
 18    END LOOP;
 19  END;
 20  /
charArray Varray:1:
charArray Varray:2:
charArray Varray:3:
1:A
2:B
3:C
PL/SQL procedure successfully completed.
SQL>

Assing an empty table record to non-empty table record

     
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200)
  3        index by binary_integer;
  4
  5      myText_table     myTextTableType;
  6      l_empty_table    myTextTableType;
  7    begin
  8      myText_table(10) := "A";
  9      myText_table(20) := "AA";
 10      myText_table(30) := "AAA";
 11
 12      dbms_output.put_line(myText_table.count);
 13
 14      myText_table.DELETE;
 15      dbms_output.put_line(myText_table.count);
 16
 17      myText_table(15) := "some text";
 18      myText_table(25) := "some more text";
 19      dbms_output.put_line(myText_table.count);
 20
 21      myText_table := l_empty_table;
 22      dbms_output.put_line(myText_table.count);
 23
 24    end;
 25    /
3
0
2
0
PL/SQL procedure successfully completed.

behavior of NULL nested tables.

     
SQL>
SQL> DECLARE
  2    TYPE wordTableType IS TABLE OF VARCHAR2(50);
  3
  4   
  5    myTable1 wordTableType;
  6
  7   
  8    myTable2 wordTableType := wordTableType();
  9  BEGIN
 10    IF myTable1 IS NULL THEN
 11      DBMS_OUTPUT.PUT_LINE("myTable1 is NULL");
 12    ELSE
 13      DBMS_OUTPUT.PUT_LINE("myTable1 is not NULL");
 14    END IF;
 15
 16    IF myTable2 IS NULL THEN
 17      DBMS_OUTPUT.PUT_LINE("myTable2 is NULL");
 18    ELSE
 19      DBMS_OUTPUT.PUT_LINE("myTable2 is not NULL");
 20    END IF;
 21  END;
 22  /
myTable1 is NULL
myTable2 is not NULL
PL/SQL procedure successfully completed.
SQL>
SQL>

Call delete method on table record

     
SQL>
SQL> declare
  2      type myTextTableType is table of varchar2(200)
  3        index by binary_integer;
  4
  5      myText_table     myTextTableType;
  6      l_empty_table    myTextTableType;
  7    begin
  8      myText_table(10) := "A";
  9      myText_table(20) := "AA";
 10      myText_table(30) := "AAA";
 11
 12      dbms_output.put_line(myText_table.count);
 13
 14      myText_table.DELETE;
 15      dbms_output.put_line(myText_table.count);
 16
 17    end;
 18    /
3
0
PL/SQL procedure successfully completed.
SQL>

Call delete method with number on table record

     
SQL>
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200)
  3        index by binary_integer;
  4
  5      myText_table     myTextTableType;
  6      l_empty_table    myTextTableType;
  7    begin
  8      myText_table(10) := "A";
  9      myText_table(20) := "AA";
 10      myText_table(30) := "AAA";
 11
 12      dbms_output.put_line(myText_table.count);
 13
 14      myText_table.DELETE(20);
 15      dbms_output.put     (myText_table.count);
 16
 17    end;
 18    /
3
PL/SQL procedure successfully completed.
SQL>

Clear the table of varchar2

    
SQL>
SQL> -- delete Pl/SQL table records
SQL>
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200) index by binary_integer;
  3
  4      l_text_table     myTextTableType;
  5      l_empty_table    myTextTableType;
  6    begin
  7      l_text_table(10) := "A value";
  8      l_text_table(20) := "Another value";
  9      l_text_table(30) := "Yet another value";
 10
 11
 12      l_text_table := l_empty_table;
 13      dbms_output.put  ("Once we assign our populated table to an empty ");
 14      dbms_output.put_line("table, we end up with " || l_text_table.count);
 15      dbms_output.put_line(" varchar2s ");
 16
 17    end;
 18    /
Once we assign our populated table to an empty table, we end up with 0
varchar2s
PL/SQL procedure successfully completed.
SQL>

dbms_sql.varchar2_table type

     
SQL>
SQL>
SQL>  declare
  2      l_names dbms_sql.varchar2_table;
  3    begin
  4      l_names(1) := "W";
  5      l_names(2) := "J";
  6      l_names(3) := "C";
  7
  8      for idx in 1 .. l_names.COUNT loop
  9        dbms_output.put_line("Name (" || idx || ") is " || l_names(idx));
 10      end loop;
 11    end;
 12    /
Name (1) is W
Name (2) is J
Name (3) is C
PL/SQL procedure successfully completed.
SQL>

Declare a nested table with null values.

    
SQL>
SQL> DECLARE
  2    TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR);
  3
  4    charArray charArrayType := charArrayType(NULL,NULL,NULL);
  5  BEGIN
  6
  7    FOR i IN 1..3 LOOP
  8      dbms_output.put_line("charArray:"||i||":"||charArray(i));
  9    END LOOP;
 10  END;
 11  /
charArray:1:
charArray:2:
charArray:3:
PL/SQL procedure successfully completed.
SQL>

Define "table of varchar2" as data type and insert data

    
SQL>
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200) index by binary_integer;
  3
  4      l_text_table     myTextTableType;
  5      l_empty_table    myTextTableType;
  6    begin
  7      l_text_table(10) := "A value";
  8      l_text_table(20) := "Another value";
  9      l_text_table(30) := "Yet another value";
 10
 11      l_text_table(15) := "some text";
 12      l_text_table(25) := "some more text";
 13      dbms_output.put     ("After some assignments, we end up with ");
 14      dbms_output.put_line(l_text_table.count || " varchar2s ");
 15      dbms_output.put_line("-");
 16
 17    end;
 18    /
After some assignments, we end up with 5 varchar2s
-
PL/SQL procedure successfully completed.
SQL>

Delete element in table of varchar2

    

SQL> -- delete Pl/SQL table records
SQL>
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200) index by binary_integer;
  3
  4      l_text_table     myTextTableType;
  5      l_empty_table    myTextTableType;
  6    begin
  7      l_text_table(10) := "A value";
  8      l_text_table(20) := "Another value";
  9      l_text_table(30) := "Yet another value";
 10
 11      l_text_table.DELETE(20);
 12      dbms_output.put     ("After using the DELETE operator on the second ");
 13      dbms_output.put     ("record (ie, DELETE(20), we have "||l_text_table.count);
 14      dbms_output.put_line(" varchar2s");
 15      dbms_output.put_line("-");
 16
 17    end;
 18    /
After using the DELETE operator on the second record (ie, DELETE(20), we have 2 varchar2s
-
PL/SQL procedure successfully completed.
SQL>
SQL>

Dynamic initialization and assignment in the execution section.

    
SQL> DECLARE
  2    TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR);
  3
  4    charArray charArrayType := charArrayType();
  5  BEGIN
  6    FOR i IN 1..3 LOOP
  7      charArray.EXTEND;
  8      IF i = 1 THEN
  9        charArray(i) := "A";
 10      ELSIF i = 2 THEN
 11        charArray(i) := "B";
 12      ELSIF i = 3 THEN
 13        charArray(i) := "C";
 14      END IF;
 15    END LOOP;
 16
 17    FOR i IN 1..3 LOOP
 18      dbms_output.put     (i||":");
 19      dbms_output.put_line(charArray(i));
 20    END LOOP;
 21  END;
 22  /
1:A
2:B
3:C
PL/SQL procedure successfully completed.

first last next

     
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL>  set serverout on
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200)
  3        index by binary_integer;
  4
  5      myText_table     myTextTableType;
  6      myIndex       number;
  7    begin
  8      for emp_rec in (select * from emp) loop
  9        myText_table(emp_rec.empno) := emp_rec.ename;
 10      end loop;
 11
 12      myIndex := myText_table.first;
 13      loop
 14        exit when myIndex is null;
 15        dbms_output.put_line(myIndex ||":"|| myText_table(myIndex));
 16        myIndex := myText_table.next(myIndex);
 17      end loop;
 18    end;
 19    /
7369:SMITH
7499:ALLEN
7521:WARD
7566:JONES
7654:MARTIN
7698:BLAKE
7782:CLARK
7788:SCOTT
7839:KING
7844:TURNER
7876:ADAMS
7900:JAMES
7902:FORD
7934:MILLER
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>

index by binary integer or by varchar2

    
SQL> declare
  2   type charListType is table of number index by varchar2(100);
  3   vc charListType;
  4   type numberListType is table of number index by binary_integer;
  5   n numberListType;
  6   t number;
  7   begin
  8       t := dbms_utility.get_time;
  9       for i in 1 .. 1000 loop
 10           n(i*1000) := i;
 11       end loop;
 12       dbms_output.put_line("Index by Number : "||(dbms_utility.get_time-t));
 13       t := dbms_utility.get_time;
 14       for i in 1 .. 1000 loop
 15           vc(i*1000) := i;
 16       end loop;
 17       dbms_output.put_line("Index by Varchar2: "||(dbms_utility.get_time-t));
 18   end;
 19  /
Index by Number : 0
Index by Varchar2: 1
PL/SQL procedure successfully completed.
SQL>
SQL>

Insert elements into table of varchar2

    
SQL>
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200) index by binary_integer;
  3
  4      l_text_table     myTextTableType;
  5      l_empty_table    myTextTableType;
  6    begin
  7      l_text_table(10) := "A value";
  8      l_text_table(20) := "Another value";
  9      l_text_table(30) := "Yet another value";
 10
 11      l_text_table(15) := "some text";
 12      l_text_table(25) := "some more text";
 13      dbms_output.put     ("After some assignments, we end up with ");
 14      dbms_output.put_line(l_text_table.count || " varchar2s ");
 15      dbms_output.put_line("-");
 16
 17    end;
 18    /
After some assignments, we end up with 5 varchar2s
-
PL/SQL procedure successfully completed.
SQL>
SQL>

Reference count property of table record

     
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200)
  3        index by binary_integer;
  4
  5      myText_table     myTextTableType;
  6      l_empty_table    myTextTableType;
  7    begin
  8      myText_table(10) := "A";
  9      myText_table(20) := "AA";
 10      myText_table(30) := "AAA";
 11
 12      dbms_output.put_line(myText_table.count);
 13
 14    end;
 15    /
3
PL/SQL procedure successfully completed.
SQL>

Table of varchar2 delete all

    

SQL>
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200) index by binary_integer;
  3
  4      l_text_table     myTextTableType;
  5      l_empty_table    myTextTableType;
  6    begin
  7      l_text_table(10) := "A value";
  8      l_text_table(20) := "Another value";
  9      l_text_table(30) := "Yet another value";
 10
 11      l_text_table.DELETE;
 12      dbms_output.put     ("After using the DELETE operator, we have ");
 13      dbms_output.put_line(l_text_table.count || " varchar2s ");
 14      dbms_output.put_line("-");
 15
 16    end;
 17    /
After using the DELETE operator, we have 0 varchar2s
-
PL/SQL procedure successfully completed.
SQL>
SQL>

Table of varchar2 element count

    
SQL>
SQL>
SQL>  declare
  2      type myTextTableType is table of varchar2(200) index by binary_integer;
  3
  4      l_text_table     myTextTableType;
  5      l_empty_table    myTextTableType;
  6    begin
  7      l_text_table(10) := "A value";
  8      l_text_table(20) := "Another value";
  9      l_text_table(30) := "Yet another value";
 10
 11      dbms_output.put_line("We start with " || l_text_table.count || " varchar2s ");
 12      dbms_output.put_line("-");
 13
 14    end;
 15    /
We start with 3 varchar2s
-
PL/SQL procedure successfully completed.
SQL>
SQL>

Varchar table indexed by BINARY_INTEGER

     
SQL>
SQL> DECLARE
  2    TYPE CharacterTab IS TABLE OF VARCHAR2(10)
  3      INDEX BY BINARY_INTEGER;
  4    v_Characters CharacterTab;
  5  BEGIN
  6    v_Characters(0) := "H";
  7    v_Characters(-7) := "S";
  8    v_Characters(3) := "S";
  9  END;
 10  /
PL/SQL procedure successfully completed.
SQL>
SQL>

VARRAYs, nested tables, index-by tables

    
-- VARRAYs, nested tables, index-by tables
DECLARE
   TYPE name_nested_typ IS TABLE OF VARCHAR2(20);
   first_name_table name_nested_typ;
   TYPE             name_index_by_typ IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
   last_name_table  name_index_by_typ;
BEGIN
   
   first_name_table := name_nested_typ("A", "B","C"); 
   first_name_table.EXTEND;
   first_name_table(4) := "Ringo";
   last_name_table(3) := "Smith";
   DBMS_OUTPUT.PUT_LINE(first_name_table(4));
END;

You cannot traverse an associative array until elements are initialized.

    
SQL>
SQL> SET ECHO ON
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL>
SQL> DECLARE
  2
  3    
  4    TYPE charArrayType IS TABLE OF VARCHAR2(5 CHAR) INDEX BY BINARY_INTEGER;
  5
  6    
  7    charArray charArrayType;
  8
  9  BEGIN
 10
 11     
 12      DBMS_OUTPUT.PUT_LINE(charArray(1));
 13
 14  END;
 15  /