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!

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

S567Mar 5 2020 — edited Mar 5 2020

I am working on oracle 11g.

When i trying to execute the below sql where i am fetching the stage numbers for a customer in list of values and getting this error. seems the sum of length of stage numbers it is crossing the limit of Oracle to fit

SELECT h.old_cust customer_id

,TRIM(BOTH ',' FROM lov(s.stage_number)) stage_number

FROM xpref h LEFT JOIN ppref c ON (h.old_cust = c.new_cust )

LEFT JOIN xpref s ON (h.old_cust = s.new_cust )

WHERE h.old_cust ='4567'

GROUP BY h.old_cust;

when i tried to execute with out group by and find the sum of length of stage numbers for this customer it is giving me 5261860.

Thanks,

Added on Mar 5 2020

11 comments

1,060 views