Oracle Collection Functions
Version 11.2.0.3

General Information
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
Built-in Functions
Collections
Date Functions
Nested Tables
Numeric Functions
String Functions
Timestamp
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-2013 Daniel A. Morgan All Rights Reserved