Oracle Collection Functions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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

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

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
Cast
Character Functions
Collections
Conversion Functions
Date Functions
Miscellaneous Functions
Nested Table Functions
Numeric Functions
Object Tables
OLAP Functions
String Functions
Timestamp Functions
Types

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