#

| Chapter 7 New and Enhanced Built-in Packages in Oracle8i |

## 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.

7.3 DBMS_RLS: Implementing Fine-Grained Access Control |  | 7.5 LOB Enhancements

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.