Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 1 (11.1)

Part Number B28419-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

203 UTL_COLL

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

This chapter contains the following topics:


Summary of UTL_COLL Subprograms

Table 203-1 UTL_COLL Package Subprograms

Subprogram Description
IS_LOCATOR Function
Determines whether a collection item is actually a locator or not


IS_LOCATOR Function

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

Syntax

UTL_COLL.IS_LOCATOR (
   coln IN STANDARD) 
  RETURNS BOOLEAN;

Pragmas

Asserts WNDS, WNPS and RNPS pragmas

Parameters

Table 203-2 IS_LOCATOR Function Parameters

Parameter Description
coln Nested table or varray item.

Return Values

Table 203-3 IS_LOCATOR Function Return Values

Return Value Description
1 Collection item is indeed a locator.
0 Collection item is not a locator.

Examples

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;