Initialize table collection of varchar2 : Table of Varchar2 « Collections « Oracle PL/SQL Tutorial

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

SQL>
SQL> CREATE TYPE names_t AS TABLE OF VARCHAR2 (100);
  2  /

Type created.

SQL>
SQL> CREATE TYPE authors_t AS TABLE OF VARCHAR2 (100);
  2  /

Type created.

SQL>
SQL> CREATE TABLE favorite_authors (name varchar2(200))
  2  /

Table created.

SQL>
SQL> BEGIN
  2     INSERT INTO favorite_authors VALUES ('R');
  3
  4     INSERT INTO favorite_authors VALUES ('T');
  5
  6     INSERT INTO favorite_authors VALUES ('T');
  7
  8     COMMIT;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2     scifi_favorites authors_t := authors_t ('S', 'O', 'G');
  3  BEGIN
  4     FOR rec IN  (SELECT column_value favs FROM TABLE (CAST (scifi_favorites AS  names_t))
  5                  UNION
  6                  SELECT NAME FROM favorite_authors)
  7     LOOP
  8        DBMS_OUTPUT.put_line (rec.favs);
  9     END LOOP;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> DROP TYPE authors_t force;

Type dropped.

SQL> DROP TYPE names_t force;

Type dropped.

SQL> DROP table favorite_authors;

Table dropped.

SQL>
SQL>
SQL>