Oracle UTL_COLL Demos

Oracle UTL_COLL Version 11.2.0.3

General Information

Purpose

Enables PL/SQL programs to use collection locators to query and update

AUTHID

DEFINER

Dependencies

DBMS_UTL_COLL_LIB

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) 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