1  DECLARE
  2    TYPE v_ref_type IS REF CURSOR;
  3    v_cur v_ref_type;
  4    TYPE v_tab_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
  5    v_tab v_tab_type;
  6    v_query_string VARCHAR2(2000);
  7    j NUMBER := 0;
  8  begin
  9  -- Building the SQL string using unions
 10    v_query_string := 'SELECT ''xx'' val FROM DUAL';
 11    FOR i IN 1..10 LOOP
 12      -- Inserting some dummy values. At loop numer 8, insert a calculated value
 13      -- namely: 72 (80-8)
 14      IF i = 8 THEN
 15        v_query_string := v_query_string||' UNION SELECT to_char('||(i*10-i)||') val FROM DUAL';
 16      ELSE
 17        v_query_string := v_query_string||' UNION SELECT '''||chr(65+i)||'Y'' val FROM DUAL';
 18      END IF;
 19    END LOOP;
 20  -- In the end, add the ORDER BY
 21    v_query_string := v_query_string||' ORDER BY 1 ASC';
 22  -- Open a cursor using the SQL string we just built
 23    OPEN v_cur for v_query_string;
 24    LOOP
 25      j := j+1;
 26      FETCH v_cur INTO v_tab(j);
 27      EXIT WHEN v_cur%NOTFOUND;
 28    END LOOP;
 29    CLOSE v_cur;
 30    FOR  a IN 1..v_tab.COUNT LOOP
 31       dbms_output.put_line(v_tab(a)||' is element '||a);
 32    END LOOP;
 33  EXCEPTION
 34    WHEN OTHERS THEN
 35      dbms_output.put_line('Error: '||sqlerrm);
 36      IF v_cur%ISOPEN THEN
 37        CLOSE v_cur;
 38      END IF;
 39* END;
SQL> /
72 is element 1
BY is element 2
CY is element 3
DY is element 4
EY is element 5
FY is element 6
GY is element 7
HY is element 8
JY is element 9
KY is element 10
xx is element 11

PL/SQL procedure successfully completed.

SQL>