for index between first and last : Table of Varchar2 « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. Table of Varchar2

SQL>
SQL> CREATE OR REPLACE TYPE strings_nt IS TABLE OF VARCHAR2(100);
  2  /

Type created.

SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE employees_pkg
  2  IS
  3     vancouver_employees strings_nt := strings_nt ('R', 'H', 'D', 'S', 'C');
  4     newyork_employees   strings_nt := strings_nt ('H', 'S', 'A');
  5     boston_employees    strings_nt := strings_nt ('S', 'D');
  6
  7     PROCEDURE show_employees (
  8        title_in   IN   VARCHAR2
  9      , employees_in    IN   strings_nt
 10     );
 11  END;
 12  /

Package created.

SQL> SHO ERR
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY employees_pkg
  2  IS
  3     PROCEDURE show_employees (
  4        title_in   IN   VARCHAR2
  5      , employees_in    IN   strings_nt
  6     )
  7     IS
  8     BEGIN
  9        DBMS_OUTPUT.put_line (title_in);
 10
 11        FOR indx IN employees_in.FIRST .. employees_in.LAST
 12        LOOP
 13           DBMS_OUTPUT.put_line (indx || ' = ' || employees_in (indx));
 14        END LOOP;
 15
 16        DBMS_OUTPUT.put_line ('_');
 17     END show_employees;
 18  END;
 19  /

Package body created.

SQL> SHOw error
No errors.
SQL>
SQL>
SQL> DECLARE
  2     distinct_employees strings_nt := strings_nt ();
  3
  4     PROCEDURE bpl (val IN BOOLEAN, str IN VARCHAR2)
  5     IS
  6     BEGIN
  7        IF val
  8        THEN
  9           DBMS_OUTPUT.put_line (str || '-TRUE');
 10        ELSIF NOT val
 11        THEN
 12           DBMS_OUTPUT.put_line (str || '-FALSE');
 13        ELSE
 14           DBMS_OUTPUT.put_line (str || '-NULL');
 15        END IF;
 16     END;
 17  BEGIN
 18
 19     employees_pkg.vancouver_employees.EXTEND;
 20     employees_pkg.vancouver_employees(employees_pkg.vancouver_employees.LAST) := 'ROBERT HARRIS';
 21
 22     distinct_employees := SET (employees_pkg.vancouver_employees);
 23
 24     employees_pkg.show_employees ('FULL SET', employees_pkg.vancouver_employees);
 25
 26     bpl (employees_pkg.vancouver_employees IS A SET, 'My employees distinct?');
 27     bpl (employees_pkg.vancouver_employees IS NOT A SET, 'My employees NOT distinct?');
 28
 29     employees_pkg.show_employees ('DISTINCT SET', distinct_employees);
 30
 31     bpl (distinct_employees IS A SET, 'SET of employees distinct?');
 32     bpl (distinct_employees IS NOT A SET, 'SET of employees NOT distinct?');
 33     DBMS_OUTPUT.PUT_LINE ('');
 34
 35  END;
 36  /

PL/SQL procedure successfully completed.

SQL>