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;

Related Topics
All Functions
Analytic Functions
Built-in Functions
Cast
Character Functions
Collections
Conversion Functions
Date Functions
Miscellaneous Functions
Nested Table Functions
Numeric Functions
Object Tables
OLAP Functions
String Functions
Timestamp Functions
Types
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx