7.4 UTL_COLL: Using Collection Locators

The UTL_COLL package lets PL/SQL programs use collection locators to query and update. This package currently has only a single program: the IS_LOCATOR function. It determines whether a collection item is actually a locator. The header for this program is:

UTL_COLL.IS_LOCATOR (collection IN ANY) 
   RETURNS BOOLEAN;

where collection is a nested table or variable array. This function returns TRUE if the collection is a locator, FALSE if the collection is not a locator. It asserts the WNDS (writes no database state), WNPS (writes no program state), and RNPS (reads no package state) pragmas; thus, it can be used within SQL.

At the time of table creation, the user may specify that a collection locator is to be returned when a nested table column or attribute is fetched. Use UTL_COLL.IS_LOCATOR in your PL/SQL program to check whether a nested table attribute or variable is locator based. You might want to do this before performing certain collection operations that could cause a large nested table value to be materialized in memory.

The following script demonstrates the use of UTL_COLL.IS_LOCATOR. Its data is based on a true story drawn from the pages of a major Midwestern newspaper. It seems that in a recent election, a candidate pledged firm opposition to an expansion of gambling in the state. After receiving hundreds of thousands of dollars in "contributions" from various gambling forces in that state, however, this candidate (who won the election) changed that position and supported the expansion of gambling venues.

/* Filename on companion disk: utlcoll.sql */
CREATE OR REPLACE TYPE legal_bribe_t as TABLE OF NUMBER; 
/ 
 
CREATE OR REPLACE TYPE legal_briber_t AS OBJECT ( 
  source VARCHAR2(100), legal_bribes legal_bribe_t ); 
/ 
 
CREATE TABLE legal_briber OF legal_briber_t 
   NESTED TABLE legal_bribes STORE AS nt_bribes; 

INSERT INTO legal_briber VALUES (
   'RIVERBOAT CASINO INDUSTRY', 
   legal_bribe_t (385584, 632000, 267000)
   ); 

CREATE TABLE legal_briber1 OF legal_briber_t 
   NESTED TABLE legal_bribes STORE AS nt_bribes1 
   RETURN LOCATOR; 
 
INSERT INTO legal_briber1 VALUES (
   'RIVERBOAT CASINO INDUSTRY', 
   legal_bribe_t (385584, 632000, 267000)
   ); 

DECLARE
  pocket_liners legal_bribe_t; 
  pocket_liners1 legal_bribe_t; 
BEGIN 
  SELECT legal_bribes INTO pocket_liners 
    FROM legal_briber 
   WHERE source = 'RIVERBOAT CASINO INDUSTRY'; 
 
  SELECT legal_bribes INTO pocket_liners1 
    FROM legal_briber1 
   WHERE source = 'RIVERBOAT CASINO INDUSTRY'; 
 
  /* Boolean "put line" procedure */
  bpl (UTL_COLL.IS_LOCATOR (pocket_liners)); 
 
  bpl (UTL_COLL.IS_LOCATOR (pocket_liners1)); 
END;
/

How would you put this function to use in your code? Here is one example:

/* Filename on companion disk: utlcoll2.sql */
CREATE OR REPLACE FUNCTION getpets_like
   (petlist IN Pettab_t, like_str IN VARCHAR2)
   RETURN pettab_t
IS
   list_to_return Pettab_t := Pettab_t();
   onepet Pet_t;
   counter PLS_INTEGER := 1;
BEGIN
   IF UTL_COLL.IS_LOCATOR (petlist)
   THEN
      FOR theRec IN
         (SELECT VALUE(petList) apet
           FROM TABLE(CAST(petlist AS Pettab_t)) petList
          WHERE petList.name LIKE like_str)
      LOOP
         list_to_return.EXTEND;
         list_to_return(counter) := theRec.apet;
         counter := counter + 1;
      END LOOP;
   ELSE
      FOR i IN 1..petlist.COUNT
      LOOP
         IF petlist(i).name LIKE like_str
         THEN
            list_to_return.EXTEND;
            list_to_return(i) := petlist(i);
         END IF;
      END LOOP;
   END IF;
   RETURN list_to_return;
END;
/

The getpets_like function accepts a list of pets and a filter or "like string." It returns a list of pets whose names match that filter. It uses the UTL_COLL.IS_LOCATOR function to optimize access to the nested table. If you have a locator, then the TABLE CAST operators are used to access the table contents via SQL. Otherwise, a numeric FOR loop is used to access each row individually. For large collections that return a locator, the TABLE CAST approach should be more efficient.