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>