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

This PL/SQL tutorial continues the introduction to the use of collections by looking at varrays.

Oracle PL/SQL Collections

We've already seen that there are 3 different types of collections: • nested tables - covered in part 10 of this Oracle PL/SQL tutorial • varrays (varying arrays) - covered in this tutorial • associative arrays - covered in part 12 

PL/SQL Varrays

Varrays are one-dimensional, variable length arrays, with the maximum length defined in the declaration. These are used when it is known in advance that you will only need to store a few items and you know in advance the maximum number that you will need to hold. A good example of the use of these would be for storing telephone numbers for employees - each person is unlikely to have more than half a dozen telephone numbers. We can create a table for storing these as follows:- TYPE phone_nos_tab IS VARRAY(6) OF VARCHAR2(20);     phone_nos phone_no_tab_typ; The preceding 2 statements create an uninitialised array that is available within our PL/SQL session. Inserting and retrieving data is done in the same way as with nested tables. First of all we have to initialise the array, then we can insert data into and retrieve data from the varray. As with nested tables, if you attempt to read from or write to an uninitialised varray collection, Oracle generates an error which causes an exception to be raised, as in the following examples: SQL> declare 2     TYPE phone_no_tab IS VARRAY(6) OF VARCHAR2(20) ; 3     phone_nos phone_no_tab; 4 begin 5     dbms_output.put_line('phone_no(1) is '||phone_nos(1)); 6 end; 7 / declare * ERROR at line 1: ORA-06531: Reference to uninitialized collection ORA-06512: at line 5 SQL> declare 2     TYPE phone_no_tab IS VARRAY(6) OF VARCHAR2(20) ; 3     phone_nos phone_no_tab; 4 begin 5     phone_nos(1) := 1; 6 end; 7 / declare * ERROR at line 1: ORA-06531: Reference to uninitialized collection ORA-06512: at line 5 We can initialise PL/SQL varrays either in the declaration or separately. If the varray is only partially initialised we have to use the EXTEND method to define extra cells. Let's see how to do this in the next two examples. SQL> DECLARE 2     TYPE phone_no_tab IS VARRAY(6) OF VARCHAR2(20); 3     phone_nos phone_no_tab := phone_no_tab('0845 003 1320'); -- only 1st cell initailised 4 BEGIN 5     phone_nos (1) := '0117 942 2508'; 6     DBMS_OUTPUT.PUT_LINE('phone_no(1) is '||phone_nos(1)); 7 END; 8 / phone_no(1) is 21 This example partially initialised the table when it was declared (line 3) . Notice the use of the constructor for our collection on line 3 of our anonymous block. This created one cell in the array. We could have used the constructor to define as many elements as we liked, to do this we would just have to separate each element by a comma. For example to define two elements, the syntax would be like this: phone_nos phone_no_tab := phone_no_tab('0845 003 1230', '942 2508'); This next example demonstrates the use of the EXTEND method for PL/SQL varrays. SQL> DECLARE 2     TYPE phone_no_tab IS VARRAY(6) OF VARCHAR2(20); 3     phone_nos phone_no_tab; 4 BEGIN 5     phone_nos := phone_no_tab(); --create empty varray 6     phone_nos.EXTEND(2); -- create first 2 cells 7     phone_nos(1) := '0117 942 2508'; 8     DBMS_OUTPUT.PUT_LINE('phone_no(1) is '||phone_nos(1)); 9 END; 10 / phone_no(1) is 0117 942 2508 Note the use of the constructor again on line 5, without this we would get the same error as before (ORA- 06531 Reference to uninitialized collection). This is because we created a PL/SQL varray with no elements, so we have to use the EXTEND method to create one or more NULL elements (in this example we created two) . The EXTEND method works the same way as it does with PL/SQL nested tables - 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 the following example: SQL> DECLARE 2     TYPE phone_no_tab IS VARRAY(6) OF VARCHAR2(20); 3     phone_nos phone_no_tab := phone_no_tab(); 4 BEGIN 5     phone_nos.EXTEND; -- one element created 7     phone_nos(1) := '0117 942 2508'; -- assign a value 8     phone_nos.EXTEND(2,1); -- copy 1st element to new elements 3&4 9     DBMS_OUTPUT.PUT_LINE('phone_no(4) is '||phone_nos(4)); 10 END; 11 / phone_no(4) is 0117 942 2508 On line 8, we specified that we two new elements should be created and that each new element should have the same value as the first element (the 2nd parameter specifies the cell which should be copied). As with nested tables 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. However unlike nested tables, varrays have an upper limit, so if we attempt to add more rows than the upper limit this causes the error ORA-06532: Subscript outside of limit. So far in this PL/SQL tutorial we've seen how to initialise varrays and add new elements. The next example shows what happens when we reference an element that doesn't exist after we've initialised the array: SQL> DECLARE 2     TYPE phone_no_tab IS VARRAY(6) OF VARCHAR2(20); 3     phone_nos phone_no_tab(); -- create 1 element 4 BEGIN 5     phone_nos.EXTEND(2); -- add 2 more 6     DBMS_OUTPUT.PUT_LINE('phone_no(4) is '||phone_nos(4)); 8 END; 9 / declare * ERROR at line 1: ORA-06533: Subscript beyond count ORA-06512: at line 6 As you can see Oracle raises an exception and we get the error ORA-06533. Let's finish by having a look at the other methods we can use on varrays in PL/SQL. COUNT: This function returns the number of elements (cells) in the collection DELETE: This deletes all the elements in the collection as varrays cannot be sparse. After deleting the whole collection any subsequent attempt to read or write to an element in the collection gives the following error: "ORA-06533: Subscript beyond count". EXISTS(n) : returns TRUE if the specified element has been created and not deleted, otherwise FALSE TRIM(n) : deletes the last n (default 1) elements in the PLSQL collection. An Oracle error (ORA- 06533: Subscript beyond count) is generated if you attempt to TRIM elements that have already been TRIMmed or if the varray has been DELETEd. The next tutorial looks at PL/SQL associative arrays. PL/SQL tutorial Part 10 - nested tables                                                                    PL/SQL tutorial Part 12  --------------------------------------- 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.