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

Home
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


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Tips by Burleson

PL/SQL NESTED Tables

A “nested” table can be thought of as a single-column table that can either be in memory, or as a column in a database table.  A nested table is very similar to a VARRAY except that the order of the elements is not static.  Elements can be deleted or added anywhere in the nested table where as a VARRAY can only add or delete elements from the end of the array.  Because a nested table can contain empty elements, it is know as a sparse collection.  Nested tables can be declared in the PL/SQL block or in the database.  If the nested table will be used in the database, the type definition must be in the database as shown below.

create or replace type auth_table as
                  table of author.author_key%type;
 

type number_tab as table of number; 

Notice that no boundary is defined for a nested table.  A nested table has no set bounds other than the memory available to the database user.

As with a VARRAY, the nested table must also be initialized.

numb_list  number_tab := number_tab();
numb_list2 number_tab := number_tab(23,56,34,890,21);

The first line of the above example initializes an empty table while the second line initializes the table and loads the listed numbers into the table.  Once initialized, the nested table is manipulated in the same manner as the VARRAY

SQL> declare
  2    type number_tab is table of number;
  3    numb_list number_tab := number_tab(23,56,34,890,21);
  4  begin
  5    for indx in numb_list.first..numb_list.last loop
  6      dbms_output.put_line(numb_list(indx)
);
  7    end loop;
  8    numb_list.delete(2);
  9    numb_list.delete(4);
 10    for indx in numb_list.first..numb_list.last loop
 11        dbms_output.put_line(numb_list(indx)
);
 12    end loop;
 13  end;
 14  / 

23
56
34
890
21
23 

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 11

Using a sparse collection is causing us a few problems during execution as shown by the “no data found” error message.  In the example above, the first FOR loop demonstrates retrieving the values in the nested table.  In lines 8 and 9, elements inside the nested table are deleted, as opposed to deleting them from the end.  When the second loop is attempted, an exception is raised when the code uses the second value.  Remember that the DELETE method  removes the element at that location but leaves the space.  With a sparse collection, the existence of an element at each location must be checked.  Below is the same example, except that the second loop verifies that the location contains an object.

SQL> declare
  2    type number_tab is table of number;
  3    numb_list number_tab := number_tab(23,56,34,890,21);
  4  begin
  5    for indx in numb_list.first..numb_list.last loop
  6      dbms_output.put_line(numb_list(indx));
  7    end loop;
  8    numb_list.delete(2);
  9    numb_list.delete(4);
 10    for indx in numb_list.first..numb_list.last loop
 11      if numb_list.exists(indx) then
 12        dbms_output.put_line(numb_list(indx));
 13      end if;
 14    end loop;
 15  end;
 16  / 

23
56
34
890
21
23
34
21

One reason to implement nested tables in your code is the ability to load the entire nested table into the database as column values.  This is a powerful feature but if you only need the collection in your PL/SQL code, you can skip some of the overhead imposed by nested tables and VARRAYs but using the memory only collection called PL/SQL tables.


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 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm


Download your Oracle scripts now:

www.oracle-script.com

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.