ORA-19011: Character string buffer too small ORA-06512 Oracle

Question

Hi I am getting the below error.What should I make change so as to accommodate the maximum size for this variable

[Error] Execution (1: 1): ORA-19011: Character string buffer too small ORA-06512: at line 4

DECLARE
c_var CLOB;
BEGIN
SELECT 
RTRIM(XMLAGG(XMLELEMENT(E,'''' ||code|| ''' AS '||code||',')ORDER BY code).extract('//text()'),',')

INTO c_var FROM (SELECT DISTINCT Code from table_A);

DBMS_OUTPUT.PUT_LINE (c_var);
END;

What type is code and what is it's value. If it is a clob, you can't just concatenate them. — Patrick Hofman
Can you cut the statement in pieces to determine what call fails? (something we can execute as well) — Patrick Hofman
@PatrickHofman I'm sorry , I didn't get you .It fails at the select statement. — Raj A
@PatrickHofman: Is there any other way to conacatenate by comma in Oracle 11.1..I want to concatenate approx 400-500 rows of one column and put it in a variable — Raj A

anandh04 anandh04 · Accepted Answer · 2014-08-14T22:22:39

If u want to concatenate the distinct values from code column in table_A. Please use the below query.

select listagg(Code, ',') within group (order by Code)
from (   select distinct Code 
          from table_A)

ORA-19011: Character string buffer too small ORA-06512 Oracle

1 Answers