4 Replies Latest reply: Sep 28, 2013 7:13 AM by SomeoneElse RSS

    Actual difference


      Hi Gurus

      I'm in the process of learning collection and unable to understand some points. I have the following code:



      create or replace

      PROCEDURE process_all_rows


         TYPE employees_aat

         IS TABLE OF employees%ROWTYPE;

            --INDEX BY PLS_INTEGER;

         l_employees employees_aat;


         SELECT *

         BULK COLLECT INTO l_employees

            FROM employees;


         FOR indx IN 1 .. l_employees.COUNT


           --  analyze_compensation

           -- (l_employees(indx));



         END LOOP;

      END process_all_rows;



      I think I'm using  nested table collection type and if you noticed  that I also remarks "Index by PLS Integer"  in my program and if  I uncomment it then it will become Associative array  collection type, Rite?

      I executed this program with and without INDEX BY PLS_INTEGER and it gives same records, My question is that in which scenario I have to use INDEX BY PLS_INTEGER clause or in which scenario I don't need to use this clause. Please guide. Thanks.


      If possible please refer me some good codes for oracle collection to understand it more properly. Thanks

        • 1. Re: Actual difference

          Associative Array (Or call it Indexed collection) are sparse. You can do this



            type tbl is table of varchar2(10) index by pls_integer;

            var tbl;


            var(100) := 'karthick';

            var(200) := 'oracle';




          Such a thing is not possible in nested PL/SQL collection type. If you use Nested collection type you need to use .extend and create 200 elements in your collection to store that 2 values. Basically when ever you want to bulk collect a result from SELECT you can use Nested PL/SQL collection and for manual assignment you can use Associative Array.

          • 2. Re: Actual difference

            Thanks for the reply. You mean to say nested table is sequential and associative array is not sequential but in which scenario we need to specify associative collection. I think we can use nested with the combination of forall and  then insert it into target name but I don't know when we usually use associative collection....

            any other thoughts?. Any good example for collection?

            • 3. Re: Actual difference

              Any other thoughts?

              • 4. Re: Actual difference

                This chapter spells it out quite nicely:


                PL/SQL Collections and Records