BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
CREATE TABLE mytable
(
JOB_REQUEST_ID NUMBER(10),
USER_ID NUMBER(6)
);
SET DEFINE OFF;
Insert into MYTABLE
(JOB_REQUEST_ID, USER_ID)
Values
(4610756, 7926);
Insert into MYTABLE
(JOB_REQUEST_ID, USER_ID)
Values
(4610756, 8318);
Insert into MYTABLE
(JOB_REQUEST_ID, USER_ID)
Values
(4610757, 7926);
Insert into MYTABLE
(JOB_REQUEST_ID, USER_ID)
Values
(4610757, 8318);
Insert into MYTABLE
(JOB_REQUEST_ID, USER_ID)
Values
(4610758, 7926);
Insert into MYTABLE
(JOB_REQUEST_ID, USER_ID)
Values
(4610758, 8318);
COMMIT;
this works fine for a comma delimited list
SELECT job_request_id,
LISTAGG (user_id, ',') WITHIN GROUP (ORDER BY user_id) user_ids
FROM mytable
GROUP BY job_request_id;
JOB_REQUEST_ID USER_IDS
4610756 7926,8318
4610757 7926,8318
4610758 7926,8318
however I'm getting an error when trying to select it into a varray I'm not quite sure what I am doing incorrectly.
SELECT job_request_id,
CAST (COLLECT (USER_ID) AS SYS.OdcinumberList) user_ids
FROM mytable
GROUP BY job_request_id;
ORA-22814: attribute or element value is larger than specified in type