Definition:
In Oracle PL/SQL, UTL_COLL is an Oracle supplied package which allows PL/SQL blocks to use collection locators. Currently, it contains only one subprogram, IS_LOCATOR, to check whether the input collection is a locator or not.
Example Syntax:
UTL_COLL.IS_LOCATOR (collection IN ANY) RETURNS BOOLEAN
In the syntax, collection is a nested table or a VARRAY (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.
Since you can use UTL_COLL.IS_LOCATOR 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.
Example Usage:
The example code below creates a Nested table TYPE_LOCATOR and uses it in PL/SQL
block to test it for locator property.
CREATE OR REPLACE TYPE TYPE_LOCATOR IS TABLE OF NUMBER;
DECLARE
L_TYPE TYPE_LOCATOR := TYPE_LOCATOR(1);
BEGIN
IF UTL_COLL.IS_LOCATOR(L_TYPE )THEN
DBMS_OUTPUT.PUT_LINE('IS LOCATOR');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT LOCATOR');
END IF;
END;
NOT LOCATOR
PL/SQL procedure successfully completed.
Related Links: