PowerMultiSet in Oracle 10g

In one of my task, I needed to find all the possible combinations of a few strings. By searching the functions in Oracle, I found an interesting and powerful function: PowerMultiSet.

Oracle says the function returns a nested table of nested tables containing all nonempty subsets (called submultisets) of the input nested table.

The submultisets are just the combinations of all elements (you're guaranteed :-) ) and will have 2ⁿ-1 sets.

Let see with an example:

create or replace type NUM_LIST_TYPE is table of number;
/
create or replace type NUM_LIST_LIST_TYPE is table of NUM_LIST_TYPE;
/

select NUM_LIST_TYPE( 1, 2, 3 ) base_set from dual;

BASE_SET
-----------------------
NUM_LIST_TYPE(1, 2, 3)

It shows three elements in the set: 1, 2 and 3. The run PowerMultiSet.

select cast( powermultiset( NUM_LIST_TYPE( 1, 2, 3 ) ) as NUM_LIST_LIST_TYPE ) power_set from dual;

POWER_SET
--------------------------------------------------------------------------------
NUM_LIST_LIST_TYPE(NUM_LIST_TYPE(1), NUM_LIST_TYPE(2), NUM_LIST_TYPE(1, 2), NUM_
LIST_TYPE(3), NUM_LIST_TYPE(1, 3), NUM_LIST_TYPE(2, 3), NUM_LIST_TYPE(1, 2, 3))

Let's make it easy to read:

1
2
1 2
3
1 3
2 3
1 2 3

However, you have to create the type that is nested table of nested tables and cast the result into it. Otherwise, you'll get an error.

select powermultiset( NUM_LIST_TYPE( 1, 2, 3 ) ) power_set from dual;

ORA-22833: Must cast a transient type to a persistent type

The number of elements in the input nested table cannot exceed 32. The number of submulties in such case would be 2³² -1, that's 4 gig.