This PLSQL tutorial continues the introduction to the use of collections in Oracle PL/SQL.
A quick recap on the basics of collections (see our earlier PLSQL tutorial (#9) for the details of the Oracle PLSQL collection types): collections are comprised of programmer-defined types and are available in 3 different forms:
Let's take a look at how we use each of these in turn.
Nested tables in Oracle are one-dimensional arrays such as a table of numbers. Let's start this PLSQL tutorial with an example.
TYPE number_table_type IS TABLE OF NUMBER ;
my_tab number_table_type;
The preceding 2 statements have created an un-initialised object-type table, (but one that is only available within our PLSQL session). Note, if you attempt to read from or write to an un-initialised table in PL/SQL, Oracle will generate an error, as in the following examples:
SQL> declare
2 TYPE number_table_type IS TABLE OF NUMBER ;
3 my_tab
number_table_type;
4 begin
5 dbms_output.put_line('my_tab(1)
is '||my_tab(1));
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5
SQL> declare
2 TYPE number_table_type IS TABLE OF NUMBER ;
3 my_tab
number_table_type;
4 begin
5 my_tab(1)
:= 1;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5
This begs the question, how do you initialise the table to avoid this error? Let's continue this PLSQL tutorial with examples of how to do this.
Option 1 is to initialise the table when it's declared.
SQL> DECLARE
2 TYPE number_table_type IS TABLE OF NUMBER ;
3 my_tab
number_table_type := number_table_type (2);
4 BEGIN
5 my_tab(1)
:= 21;
6 DBMS_OUTPUT.PUT_LINE('my_tab(1)
is '||my_tab(1));
7 END;
8 /
my_tab(1) is 21
Notice the use of the constructor for this PL/SQL type on line 3. In this instance we only created one cell in the table, however we can use the constructor to define as many elements as we like, we just have to separate each element by a comma. For example to define 10 elements the syntax would be like this:
my_tab number_table_type :=
number_table_type (256,4,9,10,5,98,7,11,489,101);
This next example in our PLSQL tutotial demonstrates option 2 - the use of the EXTEND method, but note that we still have to use the constructor to initialise the table.
SQL> DECLARE
2 TYPE number_table_type IS TABLE OF NUMBER ;
3 my_tab
number_table_type ;
4 BEGIN
5
my_tab := number_table_type();
6
my_tab.EXTEND(2);
7 my_tab(1)
:= 21;
8 DBMS_OUTPUT.PUT_LINE('my_tab(1)
is '||my_tab(1));
9 END;
10 /
my_tab(1) is 21
Note the use of the constructor again on line 5, without this we would get the same Oracle error as before (ORA-06531 Reference to uninitialized collection).
In this case we have created a PL/SQL nested-table with no elements, so we have to use EXTEND method to create one or more NULL elements in the table ( in this example we created two) . If we don't specify the number of elements to create, then just one will be created.
We can also optionally specify that each new element should be set to the same value as an existing element as in this next example in our PLSQL tutorial.
SQL> DECLARE
2 TYPE number_table_type IS TABLE OF NUMBER ;
3 my_tab
number_table_type := number_table_type (2);
4 BEGIN
5
my_tab.EXTEND(3,1);
6 DBMS_OUTPUT.PUT_LINE('my_tab(4)
is '||my_tab(4));
7 END;
9 /
my_tab(4) is 2
Line 5 of this PLSQL block, created three new elements with each new element having the same value as the element 1 (all nested-table collections start at 1).
One other point worth remembering is that we have no control over where these elements are created. Oracle automatically appends them to the existing table so that we always have a dense structure unless we specifically delete elements.
Now we've learnt how to initialise the table and add new elements to it, but what happens if we reference an element that doesn't exist after we've initialised the table We get an Oracle error (ORA-06533: Subscript beyond count) - which in turn generates an exception as in the following example:
SQL> DECLARE
2 TYPE number_table_type IS TABLE OF NUMBER ;
3 my_tab
number_table_type ;
4 BEGIN
5
my_tab := number_table_type();
6
my_tab.EXTEND(7);
7 my_tab(4)
:= 21;
8 DBMS_OUTPUT.PUT_LINE('my_tab(11)
is '||my_tab(11));
9 END;
10 /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 8
We'll finish off this PLSQL tutorial by having a look at other methods we can use on nested-tables in PL/SQL. The other methods we can use are:
COUNT: This function returns the number of elements (cells) in the collection
DELETE: This procedure deletes one or more elements in the collection - an optional start and end point specify which element(s) is (are) to be deleted. Using this method without supplying any parameters deletes the whole collection. One interesting point to note is that even though an element may have been deleted, PLSQL still allows you to write to that element. However if you attempt to read from a deleted element a NO_DATA_FOUND exception is raised.
EXISTS(n): used to determine if the specified element has been created and not deleted, returns TRUE if the element exists, FALSE if not
FIRST: returns the subscript
of the first element in the PL/SQL nested table
LAST: returns the subscript
of the last element in the PL/SQL nested table
PRIOR: returns the subscript
of the previous element in the PL/SQL nested table
NEXT : returns the subscript
of the next element in the PLSQL nested table - necessary to avoid generating
exceptions when traversing sparse collections
TRIM(n) : deletes the last n elements in the collection, n defaults to 1. TRIM can be used to remove elements that have already been DELETEd but raises an exception (ORA-06533: Subscript beyond count) if you attempt to TRIM elements that have already been TRIMmed.
In the next PLSQL tutorial we'll look at the use of the varray collection type in Oracle PL/SQL
----------
Take advantage of one of our classroom-based Oracle PLSQL training courses to extend, enhance and consolidate your knowledge of PL/SQL. See the Oracle training page for details.
-----------
PLSQL tutorial Part 9 Oracle PLSQL tutorial part 11
---------------------------------------
Looking for more Oracle tips and tricks? Subscribe to our ezine and learn how to make the most of Oracle.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses woopra.com to gather statistical information about our visitors. View woopra privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright Smartsoft Computing Ltd 2001-2009. All rights reserved.