Quick Search:
 
 The Oracle PL/SQL UTL_COLL Package      [Return To Index] Jump to:  

Term: UTL_COLL

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:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org