Q:

Inserting data from DB into array variable (PL/SQL)

I'd like to read distinct data from one attribute of a table and then insert it into array variable. Considered data is of varchar2 type. After that I'd like to use this data written in arrays in 'where' condition. So in general I'd like to do sth like this:
TYPE genreArray IS VARRAY(50) OF VARCHAR2(12);
genres genreArray;

BEGIN
    FOR a IN (SELECT DISTINCT genre FROM books) LOOP
    genres(a) := uniqe GENRE*

* of course this line is wrong because I couldn't find an answer to my question on the Internet.
The second question is wether I could use something like dynamic array where I define number of elements during execution of a program?
And my final question is if I could use it another select like:
SELECT sth WHERE sth AND GENRE=genres(i)

Assuming that 'i' is a for loop variable.
Thank you in advance for your help.

A:

If there is no particular reason to use a varray, using a nested table would be much more natural.
DECLARE
  TYPE genre_nt IS TABLE OF varchar2(12);
  l_genres genre_nt;
BEGIN
  SELECT DISTINCT genre
    BULK COLLECT INTO l_genres
    FROM books;

  FOR i IN 1 .. l_genres.count
  LOOP
    dbms_output.put_line( l_genres(i) );
  END LOOP;
END;

