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

    Actual difference

    Muzz


      Hi Gurus

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

      Code

       

      create or replace

      PROCEDURE process_all_rows

      IS

         TYPE employees_aat

         IS TABLE OF employees%ROWTYPE;

            --INDEX BY PLS_INTEGER;

         l_employees employees_aat;

      BEGIN

         SELECT *

         BULK COLLECT INTO l_employees

            FROM employees;

          

         FOR indx IN 1 .. l_employees.COUNT

         LOOP

           --  analyze_compensation

           -- (l_employees(indx));

           dbms_output.put_line(l_employees(indx).EMPLOYEE_ID);

           null;

         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
          _Karthick_


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

           

          declare

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

            var tbl;

          begin

            var(100) := 'karthick';

            var(200) := 'oracle';

          end;

          /

           

          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
            Muzz

            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
              Muzz

              Any other thoughts?

              • 4. Re: Actual difference
                SomeoneElse

                This chapter spells it out quite nicely:

                 

                PL/SQL Collections and Records