Is there an alternative to the MULTISET operator that avoids sub-queries?

Question

Suppose you have a table as follows:

CREATE TABLE EMPLOYEE_SALES
(
    EMPLOYEE_ID NUMBER,
    PRODUCT_ID  NUMBER,
    SALE_AMOUNT NUMBER
);

And suppose it is populated as follows:

+-------------+------------+-------------+
| EMPLOYEE_ID | PRODUCT_ID | SALE_AMOUNT |
+-------------+------------+-------------+
|      1      |     100    |       1.05  |
|      1      |     200    |      45.67  |
|      2      |     100    |       3.25  |
|      2      |     200    |      34.29  |
+-------------+------------+-------------+

Now, suppose I create a custom type named SALE_TYPE which represents a (PRODUCT_ID, SALE_AMOUNT) tuple:

CREATE TYPE SALE_TYPE IS OBJECT
(
  PRODUCT_ID  NUMBER,
  SALE_AMOUNT NUMBER
);

And suppose I also create a custom type named SALES_TYPE which represents a TABLE of SALE_TYPE:

CREATE TYPE SALES_TYPE IS TABLE OF SALE_TYPE;

I want to query the EMPLOYEE_SALES table group by EMPLOYEE_ID. For each EMPLOYEE_ID, I wish to create a SALES_TYPE containing that employee's sales. According to the documentation, I would do something like this:

SELECT
  EMPLOYEE_ID,
  CAST
  (
    MULTISET
    (
      SELECT
        PRODUCT_ID,
        SALE_AMOUNT
      FROM
        EMPLOYEE_SALES inner_employee_sales
      WHERE
        inner_employee_sales.employee_id = employee_sales.employee_id
    ) AS SALES_TYPE
  ) AS SALES
FROM
  EMPLOYEE_SALES
GROUP BY
  EMPLOYEE_ID

I expect this query to yield something like this:

+-------------+------------------------------+
| EMPLOYEE_ID | SALES                        |
+-------------+------------------------------+
|     1       | +------------+-------------+ |
|             | | PRODUCT_ID | SALE_AMOUNT | |
|             | +------------+-------------+ |
|             | |    100     |        1.05 | |
|             | |    200     |       45.67 | |
|             | +------------+-------------+ |
+-------------+------------------------------+
|     2       | +------------+-------------+ |
|             | | PRODUCT_ID | SALE_AMOUNT | |
|             | +------------+-------------+ |
|             | |    100     |        3.25 | |
|             | |    200     |       34.29 | |
|             | +------------+-------------+ |
+-------------+------------------------------+

Is there a way to achieve the same results without issuing a sub-query (the real query I am working on is much more complicated than this contrived example)? For example, is there something like this:

SELECT
  EMPLOYEE_ID,
  CAST
  (
    COLLECT_ALL_RECORDS_WITHIN_THE_CURRENT_GROUP(
      PRODUCT_ID,
      SALE_AMOUNT
    )
    AS SALES_TYPE
  ) AS SALES
FROM
  EMPLOYEE_SALES
GROUP BY
  EMPLOYEE_ID

Answer 1

In Oracle 10g, you can use the COLLECT operator as follows:

select employee_id, 
       cast(collect(sale_type(product_id, sale_amount)) as sales_type)
from employee_sales
group by employee_id