Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL UTL_COLL Package      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 71 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?