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!

Query runs once and now errors out. Not sure what I've done wrong

Tony DunsworthJun 15 2012 — edited Jun 18 2012
I'm reworking a query for an application we're producing. The original query left out a comments field we need for the production model, so I needed to add the comments in now that we know the original polling application is doing its thing and is stable. I rewrote the query which now needs to comb a table, match an id number and grab all of the comments associated with that id. I am using, or want to use the wm_concat function as it seems to be pretty straightforward and easy to use.

The query below ran fine twice and then started throwing the errors below on the third try.

The query is:
SELECT DISTINCT aeven.ad_sec, aeven.ad_ts, aeven.ag_id, event.ccity, event.eapt, event.edirpre, event,edirsuf, event.efeanme, event.efeatyp, aeven.eid, event.estnum, aeven.tycod, aeven.sub_tycod, aeven.udts, un_hi.unid, aeven.xdts, wm_contact(evcom.comm) FROM un_hi JOIN aeven ON un_hi.eid = aeven.eid JOIN event ON aeven.eid = event.eid JOIN evcom ON event.eid = evcom.eid WHERE aeven.curent = 'T' AND event.curent = 'T' AND (aeven.ad_ts > TO_CHAR(SYSDATE - 3/24/60, 'YYYYMMDDHH24MISS') OR aeven.udts > TO_CHAR(SYSDATE - 3/24/60, 'YYYYMMDDHH24MISS')) GROUP BY aeven.ad_sec, aeven.ad_ts, aeven.ag_id, event.ccity, event.eapt, event.edirpre, event,edirsuf, event.efeanme, event.efeatyp, aeven.eid, event.estnum, aeven.tycod, aeven.sub_tycod, aeven.udts, un_hi.unid, aeven.xdts;

The error I'm getting from SQL Developer is:

ORA-06502: PL/SQL: numeric or value error: character string buffer is too small
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30
06502. 00000 - "PL/SQL numeric or value error%s"
*Cause:
*Action:

So, I know I'm likely missing something easy for everyone, but I just can't quite see what I'm missing and why it worked fine then stopped in its tracks.

Any help to point me in the right direction is greatly appreciated.

Thanks,
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 16 2012
Added on Jun 15 2012
6 comments
423 views