Oracle UTL_COLL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Enables PL/SQL programs to use collection locators to query and update
AUTHID DEFINER
Dependencies
DBMS_UTL_COLL_LIB    
Documented Yes
First Available 8.1.5
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/utlcoll.plb
 
IS_LOCATOR
Determines whether a collection item is actually a locator or not utl_coll.is_locator(coln IN STANDARD.<TABLE_1>) RETURN BOOLEAN;
CREATE OR REPLACE TYPE CourseList AS TABLE OF VARCHAR2(64);
/

CREATE OR REPLACE TYPE MajorReq AS OBJECT (
major VARCHAR2(30), cl CourseList);
/

CREATE TABLE uw_major OF MajorReq
NESTED TABLE cl STORE AS nt_CourseList_t;

CREATE TABLE uw_majors OF MajorReq
NESTED TABLE cl STORE AS nt_CourseList_tab RETURN LOCATOR;

set describe depth all linenum on indent on

desc uw_major

desc uw_majors

INSERT INTO uw_major
VALUES
('Math', CourseList('Algebra','Geometry', 'Calculus'));

INSERT INTO uw_majors
VALUES
('Science', CourseList('Astronomy', 'Biology', 'Chemistry'));

SELECT cl FROM uw_major;

SELECT cl FROM uw_majors;

COMMIT;

CREATE OR REPLACE PROCEDURE is_coll IS
 mlist1 CourseList;
 mlist2 CourseList;
BEGIN
  SELECT cl
  INTO mlist1
  FROM uw_major
  WHERE major = 'Math';

  SELECT cl
  INTO mlist2
  FROM uw_majors
  WHERE major = 'Science';

  IF (utl_coll.is_locator(mlist1)) THEN
    dbms_output.put_line('uwmajor returns a locator');
  ELSE
    dbms_output.put_line('uwmajor does not return a locator');
  END IF;

  IF (utl_coll.is_locator(mlist2)) THEN
    dbms_output.put_line('uwmajors returns a locator');
  ELSE
    dbms_output.put_line('uwmajors does not return a locator');
  END IF;
END is_coll;
/

set serveroutput on

exec is_coll;

Related Topics
Collections
Nested Tables
Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved