Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

selecting into a varray

pollywogMar 29 2013 — edited Mar 29 2013
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
This post has been answered by BrendanP on Mar 29 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 26 2013
Added on Mar 29 2013
6 comments
926 views