Oracle | Collection Functions

Oracle Collection Functions Version 21c

General Information

Library Note

Morgan's Library Page Header

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.

Collection functions operate on nested tables and varrays

CARDINALITY

Returns the number of elements in a nested table CARDINALITY(collection IN "<TABLE_1>") RETURN PLS_INTEGER;

See Nested Table and Collections Demos

COLLECT

Takes a column of any type and creates a nested table of the input type out of the rows selected COLLECT(<column>)

conn sh/sh@pdbdev

      

      CREATE OR REPLACE TYPE phone_book_t AS TABLE OF VARCHAR2(25);

      /

      

      SELECT CAST(COLLECT(cust_main_phone_number) AS phone_book_t)

      FROM customers

      WHERE cust_id BETWEEN 1 AND 5;

      

      SELECT CAST(COLLECT(cust_credit_limit) AS phone_book_t)

      FROM customers

      WHERE cust_id BETWEEN 1 AND 5;

      

      WITH q AS (SELECT CAST(COLLECT(cust_credit_limit) AS phone_book_t)

      FROM customers

      WHERE cust_id BETWEEN 1 AND 5)

      SELECT COUNT(*) FROM TABLE(q);

POWERMULTISET

Takes a nested table and returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the input nested table

Note: This demo, and the following two, are copied from the tahiti docs but put onto a single page to make using them easier POWERMULTISET(<expression>)

conn oe/oe@pdbdev

      

      CREATE TABLE customers_demo AS

      SELECT * FROM customers;

      

      CREATE OR REPLACE TYPE cust_address_tab_typ AS

      TABLE OF cust_address_typ;

      /

      

      ALTER TABLE customers_demo

      ADD (cust_address_ntab cust_address_tab_typ, cust_address2_ntab cust_address_tab_typ)

      NESTED TABLE cust_address_ntab STORE AS cust_address_ntab_store

      NESTED TABLE cust_address2_ntab STORE AS cust_address2_ntab_store;

      

      UPDATE CUSTOMERS_DEMO cd

      SET cust_address_ntab = 

      CAST(MULTISET(SELECT cust_address

      FROM customers c

      WHERE c.customer_id =

      cd.customer_id) as cust_address_tab_typ);

      

      UPDATE CUSTOMERS_DEMO cd

      SET cust_address2_ntab = 

      CAST(MULTISET(SELECT cust_address

      FROM customers c

      WHERE c.customer_id =

      cd.customer_id) as cust_address_tab_typ);

      

      COMMIT;

      

      SELECT parent_table_name, table_name, table_type_name

      FROM user_nested_tables;

      

      CREATE OR REPLACE TYPE cust_address_tab_tab_typ

      AS TABLE OF cust_address_tab_typ;

      /

      

      SELECT CAST(POWERMULTISET(cust_address_ntab) AS cust_address_tab_tab_typ)

      FROM customers_demo;

POWERMULTISET_BY_CARDINALITY

Takes a nested table and a cardinality and returns a nested table of nested tables containing all non-empty subsets (called submultisets) of the nested table of the specified cardinality POWERMULTISET_BY_CARDINALITY(<expression>, <cardinality>)

UPDATE customers_demo

      SET cust_address_ntab = cust_address_ntab MULTISET UNION cust_address_ntab;

      

      SELECT CAST(POWERMULTISET_BY_CARDINALITY(cust_address_ntab, 2)

      AS cust_address_tab_tab_typ)

      FROM customers_demo;

SET

Converts a nested table into a set by eliminating duplicates SET(<nested_table>)

SELECT customer_id, SET(cust_address_ntab) address

      FROM customers_demo;