PLSQL Collections Examples

set serveroutput on;
DECLARE
   --TYPE list_of_names_t IS TABLE OF ap_invoices_all.invoice_num%TYPE
    TYPE list_of_names_t IS TABLE OF varchar2(16) INDEX BY PLS_INTEGER;

   happyfamily   list_of_names_t;
   l_row         PLS_INTEGER;
BEGIN
   happyfamily (2020202020) := 'Eli';
   happyfamily (-15070) := 'Steven';
   happyfamily (-90900) := 'Chris';
   happyfamily (88) := 'Veva';
   l_row := happyfamily.FIRST;

   WHILE (l_row IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (happyfamily (l_row));
      l_row := happyfamily.NEXT (l_row);
   END LOOP;
END;

Chris
Steven
Veva
Eli



--access values one by one

SET SERVEROUTPUT ON;

DECLARE
   CURSOR c_cname
   IS
      SELECT responsibility_key, responsibility_id
        FROM fnd_responsibility
       WHERE responsibility_key LIKE
                        (SELECT meaning
                           FROM fnd_lookup_values_vl
                          WHERE lookup_type = 'XX_EXP_DATE_PROGRAM_ACCESS');

   TYPE type_cname_tab IS TABLE OF VARCHAR2 (60)    --store responsibility_id
      INDEX BY BINARY_INTEGER;

   tab_cname             type_cname_tab;
   v_cname_counter       NUMBER         := 0;
   v_responsibility_id   NUMBER;
BEGIN
   FOR r_cname IN c_cname
   LOOP
      v_cname_counter := v_cname_counter + 1;
      tab_cname (v_cname_counter) := r_cname.responsibility_key;
   END LOOP;

   FOR i_cname IN 1 .. v_cname_counter
   LOOP
      SELECT responsibility_id
        INTO v_responsibility_id
        FROM fnd_responsibility
       WHERE responsibility_key = tab_cname (i_cname);

      DBMS_OUTPUT.put_line ('Responsibilty_key: ' || tab_cname (i_cname));
      DBMS_OUTPUT.put_line ('Responsibilty_id: ' || v_responsibility_id);
   END LOOP;
END;