ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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;
/
