Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson

PL/SQL Tables

As of Oracle9i PL/SQL tables  were officially named ASSOCIATIVE ARRAYS.  Most developers call associative arrays PL/SQL tables because they can not exist in the database, only in PL/SQL memory structures.  The advantage over nested tables and VARRAYs is that a PL/SQL table does not have to be extended to add elements, nor does it have to be initialized.  Best of all, elements are added in any order, in any position in the table. 

PL/SQL tables before Oracle9i could only be indexed by BINARY_INTEGER, but from Oracle9i and beyond they can be indexed either by BINARY_INTEGER or a string type.  You can conceptualize a PL/SQL table as a two-column table, the first being the index and the second being the data element.  Like the other collection types, the index value is used to locate the data element. 

In the example below, a PL/SQL table is defined and a cursor is used to load the collection and then read the elements out of the collection:

SQL> declare
  2    type book_tab is table of book.book_title%type
  3         index by binary_integer;
  4    book_list book_tab;
  5    indx      number := 1;
  6  begin
  7    for r_c3 in (select book_title from book) loop
  8      book_list(indx) := r_c3.book_title;
  9      indx := indx + 5;
 10    end loop;
 11    for xndx in book_list.first..book_list.last loop
 12      if book_list.exists(xndx) then
 13        dbms_output.put_line(book_list(xndx));
 14      end if;
 15    end loop;
 16  end;
 17  /

windows success
piano greats
cooking light
never eat meat
how to housebreak your horse

There is a lot to see in the example above.  In line 3 the PL/SQL TABLE is defined as being INDEXED BY BINARY_INTEGER .  In line 4 a variable was defined as the collection, but the collection was not initialized.  Next, a cursor is used to load the current book titles into the collection, and the indexed value was increased by 5 each time a value was added. 

Remember that the elements can be added in any order, any place in the collection.  We see that line 11 initializes a FOR loop to access each value, and the FOR loop will go to each element space that is defined in the collection.  Since this is a sparse collection (values can be deleted) element existence must be tested before accessing an element, and this test is performed on line 12.  The example demonstrates that the PL/SQL Table is very similar to the nested table  except that it does not have to be initialized, or extended.

Sometimes your data is paired with a string rather than a number and a data element is more efficiently accessed using the string.  In the PUBS database, the sales table lists order numbers and the quantity sold.  The order numbers are actually character strings.  A PL/SQL TABLE can be created to allow access to the quantity values using the order_number.  Since order_number is a varchar2 you will create the PL/SQL Table using the INDEX BY string method.

SQL> declare
  2    type orders_tab is table of number
  3          index by varchar2(20);
  4    orders_list orders_tab;
  5  begin
  6    for r_c4 in (select order_number,
  7                        quantity from sales) loop
  8      orders_list(r_c4.order_number) := r_c4.quantity;
  9    end loop;
 10    dbms_output.put_line(orders_list('O102'));
 11    dbms_output.put_line(orders_list('O109'));
 12    dbms_output.put_line(orders_list('O193'));
 13    dbms_output.put_line(orders_list('O156'));
 14  end;
 15  / 


In the example above the collection is indexed by a string that will be the order_number.  Line 3 defines this string index.  Notice that you can not use a %TYPE in this definition.  Line 8 loads the quantity values in the element defined by the order_number.  The order_number is the index for the collection.  In lines 10 through 13, the elements (quantity) stored at specified locations (order_number) are printed with dbms_output.put_line. 

As we have noted, PL/SQL collections provide the developer with powerful tools to hold and process large data sets in memory, allowing data to be pulled from the database once, and used repeatedly as needed.  If a developer needs to retrieve large numbers of rows from the database, it can be much more efficient to use a collection with a cursor and “bulk collect” the data in one operation.

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.