56
UTL_COLL

The UTL_COLL package lets PL/SQL programs use collection locators to query and update.

Summary of Subprograms

There is currently only one function supported in this package:IS_LOCATOR.

IS_LOCATOR function

This function determines whether a collection item is actually a locator or not.

Syntax

UTL_COLL.IS_LOCATOR (
   collection IN ANY) 
  RETURNS BOOLEAN;

Parameters

Table 56-1 IS_LOCATOR Function Parameters

collection

Returns

Table 56-2 IS_LOCATOR Function Returns

1

0

Pragmas

Asserts WNDS, WNPS and RNPS pragmas

Exceptions

None.

Example

CREATE OR REPLACE TYPE list_t as TABLE OF VARCHAR2(20); 
/ 
 
CREATE OR REPLACE TYPE phone_book_t AS OBJECT ( 
  pno  number, 
  ph   list_t ); 
/ 
 
CREATE TABLE phone_book OF phone_book_t 
      NESTED TABLE ph STORE AS nt_ph; 
CREATE TABLE phone_book1 OF phone_book_t 
      NESTED TABLE ph STORE AS nt_ph_1 RETURN LOCATOR; 
 
INSERT INTO phone_book VALUES(1, list_t('650-633-5707','650-323-0953')); 
INSERT INTO phone_book1 VALUES(1, list_t('415-555-1212')); 
 
CREATE OR REPLACE PROCEDURE chk_coll IS 
  plist list_t; 
  plist1 list_t; 
BEGIN 
  SELECT ph INTO plist FROM phone_book WHERE pno=1; 
 
  SELECT ph INTO plist1 FROM phone_book1 WHERE pno=1; 
 
  IF (UTL_COLL.IS_LOCATOR(plist)) THEN 
    DBMS_OUTPUT.PUT_LINE('plist is a locator'); 
  ELSE 
    DBMS_OUTPUT.PUT_LINE('plist is not a locator'); 
  END IF; 
 
  IF (UTL_COLL.IS_LOCATOR(plist1)) THEN 
    DBMS_OUTPUT.PUT_LINE('plist1 is a locator'); 
  ELSE 
    DBMS_OUTPUT.PUT_LINE('plist1 is not a locator'); 
  END IF; 
 
END chk_coll; 
 
SET SERVEROUTPUT ON 
EXECUTE chk_coll;