--The following is an example of creating PL/SQL table variables. Useful!

-- ©2004 Edward Stoever 

CREATE TABLE t1 (a NUMBER, b VARCHAR2(20), c VARCHAR2(40)) TABLESPACE users;
Table created

CREATE TABLE t2 (a NUMBER, b NUMBER, c NUMBER) TABLESPACE users;
Table created

DECLARE
  i   NUMBER := 0;
BEGIN
  FOR i IN 0 .. 13
  LOOP
     INSERT INTO t1
                 (a, b, c
                 )
          VALUES (i, dbms_random.string('l',20), dbms_random.string('l',40)
                 );
  END LOOP;

  COMMIT;
END;
PL/SQL procedure successfully completed

DECLARE
  i   NUMBER := 0;
BEGIN
  FOR i IN 0 .. 6
  LOOP
     INSERT INTO t2
                 (a, b, c
                 )
          VALUES (i, TRUNC(dbms_random.value*1000), TRUNC(dbms_random.value*100
                 );
  END LOOP;

  COMMIT;
END;
PL/SQL procedure successfully completed

SELECT * FROM t1;
A B                    C                                      
-- -------------------- ----------------------------------------
0 adrcvmjkmyzqfejrbulc jturwulykttkfeidapvtawfwcucleymbewvxydag
1 fdurchuftelcqqyloxda tyzppphdijkfsqlddacwzoouplmtuzxrbawvscvk
2 relmuxfurahnlrvijmij ofvjjgalshovenitmuogltggrtakmeehhrublmxm
3 ftpvophylkpvfppgzvuw lftayiwqzggroecajoedepfsuuohmoxwrqmdclmo
4 voqzprvpeujdlaofahhl wtmrwtvqqoofcudthbhypxkxombsxupzfzgshlqf
5 gkugomxsbwdpesilfoku nkpwnvykgtzolcfekiqpificmyklnqnvwfbufsru
6 gwgmzitoazaouswzjzhb mlfczbiqvwyqfwciocrmxlaijkvffjwrexxfyodu
7 fsfgwdwivomgkikwvbie keizuwvdlfcuvwstoxrfyhmprgnaygjitluzpaji
8 izcqkfayfbbuivuckwzs demfrxtkwcbwwuvimnoxhpuhbdldiustfvnawwbg
9 hcvcvxcvijzrluzfixjz rmdcypxtrxpupkzdmxcknkvtosyoswaojfjgrtvm
10 usqxtzuxrcpvhetgecqr lxhacyahdvpgpshgjqrgunkabuunhzxdrdnwrqfx
11 ukehvxcrvlmzpasnscoj yyluycdhjndeawnqkmswfhnwaprjfbgppqrsniof
12 kgwwhapankjxjbgzncde ejxydvwoecvvspsnhdxoaahpdnocsvwrrlenbtkl
13 dlvgackrfennckthwlbv rtfwozjuicykotuacrhirbqvgqyoxfjsmqhrkeai
14 rows selected


SELECT * FROM t2;
A  B  C
-- -- --
0 19 36
1 96 78
2 91 28
3 54 21
4 10 25
5 60 53
6 52 67
7 rows selected


/* ********************---************************* *
            Now the interesting part
* ********************---************************* */
DECLARE
  TYPE t1_tab_type IS TABLE OF t1%ROWTYPE
     INDEX BY BINARY_INTEGER;

  i              INTEGER     := 0;
  t1_tab         t1_tab_type;
  t1_tab_empty   t1_tab_type;

  CURSOR c_1
  IS
     SELECT a, b, c
       FROM t1;

  CURSOR c_2
  IS
     SELECT a, TO_CHAR (b), TO_CHAR (c)
       FROM t2;
BEGIN
  FOR c_c IN c_1
  LOOP
     t1_tab (i) := (c_c);
     i := i + 1;
  END LOOP;

  FOR c_c IN c_2
  LOOP
     t1_tab (i) := (c_c);
     i := i + 1;
  END LOOP;

  FOR my_row IN 0 .. i - 1
  LOOP
     DBMS_OUTPUT.put_line (   RPAD (TO_CHAR (t1_tab (my_row).a), 9)
                           || RPAD (t1_tab (my_row).b, 30)
                           || RPAD (t1_tab (my_row).c, 30)
                          );
  END LOOP;

  t1_tab := t1_tab_empty;  --erase t1_tab of all values
END;
/

-- OUTPUT IS JUST LIKE A UNION ALL:
0        adrcvmjkmyzqfejrbulc          jturwulykttkfeidapvtawfwcucley
1        fdurchuftelcqqyloxda          tyzppphdijkfsqlddacwzoouplmtuz
2        relmuxfurahnlrvijmij          ofvjjgalshovenitmuogltggrtakme
3        ftpvophylkpvfppgzvuw          lftayiwqzggroecajoedepfsuuohmo
4        voqzprvpeujdlaofahhl          wtmrwtvqqoofcudthbhypxkxombsxu
5        gkugomxsbwdpesilfoku          nkpwnvykgtzolcfekiqpificmyklnq
6        gwgmzitoazaouswzjzhb          mlfczbiqvwyqfwciocrmxlaijkvffj
7        fsfgwdwivomgkikwvbie          keizuwvdlfcuvwstoxrfyhmprgnayg
8        izcqkfayfbbuivuckwzs          demfrxtkwcbwwuvimnoxhpuhbdldiu
9        hcvcvxcvijzrluzfixjz          rmdcypxtrxpupkzdmxcknkvtosyosw
10       usqxtzuxrcpvhetgecqr          lxhacyahdvpgpshgjqrgunkabuunhz
11       ukehvxcrvlmzpasnscoj          yyluycdhjndeawnqkmswfhnwaprjfb
12       kgwwhapankjxjbgzncde          ejxydvwoecvvspsnhdxoaahpdnocsv
13       dlvgackrfennckthwlbv          rtfwozjuicykotuacrhirbqvgqyoxf
0        192                           363                          
1        96                            783                          
2        913                           289                          
3        545                           217                          
4        105                           25                            
5        607                           539                          
6        529                           672