Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
Syntax
Purpose
POWERMULTISET
takes as input a nested table and returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the input nested table.
expr
can be any expression that evaluates to a nested table.
If expr
resolves to null, then Oracle Database returns NULL
.
If expr
resolves to a nested table that is empty, then Oracle returns an error.
The element types of the nested table must be comparable. Please refer to "Comparison Conditions" for information on the comparability of nonscalar types.
Note:
This function is not supported in PL/SQL.Examples
First, create a datatype that is a nested table of the cust_address_tab_type
datatype:
CREATE TYPE cust_address_tab_tab_typ AS TABLE OF cust_address_tab_typ;
Now, select the nested table column cust_address_ntab
from the customers_demo
table using the POWERMULTISET
function:
SELECT CAST(POWERMULTISET(cust_address_ntab) AS cust_address_tab_tab_typ) FROM customers_demo; CAST(POWERMULTISET(CUST_ADDRESS_NTAB) AS CUST_ADDRESS_TAB_TAB_TYP) (STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID) ------------------------------------------------------------------ CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP ('514 W Superior St', '46901', 'Kokomo', 'IN', 'US'))) CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP ('2515 Bloyd Ave', '46218', 'Indianapolis', 'IN', 'US'))) CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP ('8768 N State Rd 37', '47404', 'Bloomington', 'IN', 'US'))) CUST_ADDRESS_TAB_TAB_TYP(CUST_ADDRESS_TAB_TYP(CUST_ADDRESS_TYP ('6445 Bay Harbor Ln', '46254', 'Indianapolis', 'IN', 'US'))) . . .
The preceding example requires the customers_demo
table and a nested table column containing data. Please refer to "Multiset Operators" to create this table and nested table columns.