Oracle PL/SQL Tutorial Part 10 - Using PL/SQL Collections

This tutorials continues the introduction to the use of collections in Oracle PL/SQL.

Oracle PL/SQL Collections

A quick recap on the basics of collections (see our earlier PL/SQL tutorial (#9) for the details of the collection types): collections are comprised of programmer-defined types and are available in 3 different forms: • PL/SQL nested tables (NOT the same as a nested table in the database) • varrays • associative arrays (previously known as index-by or PL/SQL tables) Let's take a look at how we use each of these in turn.

PL/SQL Nested Tables

Nested tables are one-dimensional, unbounded, homogeneous arrays, such as a table of numbers. Let’s look at an example: TYPE vc_tab IS TABLE OF VARCHAR2(20); my_tab vc_tab; The preceding 2 statements have created an uninitialised object-type table, (but one that is only available within our PL/SQL session). If you attempt to read from or write to an uninitialised table, Oracle will generate an error, as shown n 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 There are 2 ways to initialise the table to avoid this error - use a constructor method on its own or use a constructor method and the EXTEND method. Initialising a PL/SQL nested table by use of a constructor 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 example we created one cell in the table, with an initial value of 2. We can use the constructor to define as many elements as we like, we just have to separate each element by a comma. We would define 10 elements like this: my_tab number_table_type :=       number_table_type (256,4,9,10,5,98,7,11,489,101); Initialising a PL/SQL nested table by use of a constructor and the EXTEND method 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 initially created a PL/SQL nested-table with no cells so we have to use EXTEND method to create one or more NULL (empty) cells 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 where we create two new copies of the 3rd element. SQL> DECLARE 2     TYPE number_table_type IS TABLE OF NUMBER ; 3     my_tab number_table_type := number_table_type (2,7,50); 4 BEGIN 5     my_tab.EXTEND(3,2); 6     DBMS_OUTPUT.PUT_LINE('my_tab(4) is '||my_tab(4)); 7 END; 9 / my_tab(4) is 7 Line 5 of this PL/SQL block, created three new elements with each new element having the same value as the 2nd element (all nested-table collections start at 1). Oracle automatically appends the new elements to the existing table so that we always have a dense structure (unless we specifically delete elements). Let’s now see what happens if we reference an element that doesn't exist after we've initialised 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(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 As you can see in the previous example,  this generates an Oracle error (ORA-06533: Subscript beyond count) . Let’s finish by looking at the other methods we can use on PL/SQL nested-tables: • COUNT: This function returns the number of elements (cells) in the collection • DELETE: This procedure takes none, one or two parameters and (soft) deletes the whole collection if no parameters are supplied, the specified element if one parameter is supplied, or all the elements between the start and end points if two parameters are supplied. Attempting to read from a deleted element generates a NO_DATA_FOUND exception but PL/SQL allows you to restore the element by assigning a value to it. • EXISTS(n): returns TRUE if the if the specified element has been created and not deleted,  otherwise FALSE • FIRST: returns the subscript of the first element in the nested table • LAST: returns the subscript of the last element in the 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 nested table - necessary to avoid generating exceptions when traversing sparse collections • TRIM(n) : hard 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. The next tutorial looks at the use of the varray collection type in Oracle PL/SQL, but if you want to fast track your learning take one of our classroom-based training courses.  Click here. for more details PL/SQL tutorial Part 9                                                                     PL/SQL tutorial Part 11  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor- led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you make your Oracle systems faster and more reliable and save you hours searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Need help with Oracle? Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. © Copyright Smartsoft Computing Ltd 2014. All rights reserved.