Cast(Multiset((Subquery) AS Type)
CREATE OR REPLACE TYPE phone_numbers_typ AS TABLE OF VARCHAR2(100);

SELECT 
 CAST(MULTISET(
   SELECT q_ph2.phone_no
   FROM (SELECT '10001' emp_id, 1 phone_id, '123-123-1234' phone_no FROM DUAL UNION
         SELECT '10001' emp_id, 2 phone_id, '234-234-2345' phone_no FROM DUAL UNION
         SELECT '10001' emp_id, 3 phone_id, '345-345-3456' phone_no FROM DUAL) q_ph2
         ORDER BY q_ph2.phone_id)
   AS phone_numbers_typ) phone_numbers
FROM  DUAL;

Cast Multiset Function

SELECT 
 q_ph.emp_id,
 CAST(MULTISET(
   SELECT q_ph2.phone_no
   FROM (SELECT '10001' emp_id, 1 phone_id, '123-123-1234' phone_no FROM DUAL UNION
         SELECT '10001' emp_id, 2 phone_id, '234-234-2345' phone_no FROM DUAL UNION
         SELECT '10001' emp_id, 3 phone_id, '345-345-3456' phone_no FROM DUAL) q_ph2
         WHERE  q_ph.emp_id = q_ph2.emp_id
         ORDER BY q_ph2.phone_id)
   AS strings_table_type) phone_numbers
FROM
  (SELECT '10001' emp_id, 1 phone_id, '123-123-1234' phone_no FROM DUAL UNION
   SELECT '10001' emp_id, 2 phone_id, '234-234-2345' phone_no FROM DUAL UNION
   SELECT '10001' emp_id, 3 phone_id, '345-345-3456' phone_no FROM DUAL
  ) q_ph
GROUP BY q_ph.emp_id;

Cast Multiset Function


Oracle registered trademark of Oracle Corporation.

Last Revised on: February 23, 2012

  1331