CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Collection Functions
Version 11.1
 
Note: The collection functions operate on nested tables and varrays
 
CARDINALITY
Returns the number of elements in a nested table CARDINALITY(<nested_table>)
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>)
CREATE TYPE phone_book_t AS TABLE OF phone_list_typ;
/

SELECT CAST(COLLECT(phone_numbers) AS phone_book_t)
H FROM customers;
 
POWERMULTISET
Takes a nested table and returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the input nested table POWERMULTISET(<expression>)
CREATE 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 nonempty 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
Collections
Date Functions
Nested Tables
Numeric Functions
String Functions
Timestamp
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [134 users online]    © 2010 psoug.org