Character string buffer too small error in Oracle

Question

My table has the below data

Username Groupname
-------- ---------------
A        MAXADMIN
A        BUYER
B        PURCHASEMANAGER
B        FINANCECLERK

Output should be like below

Username Groupname 
-------- ----------------------------
A        MAXADMIN,BUYER
B        PURCHASEMANAGER,FINANCECLERK

I get an error ORA-19011: character string buffer too small. Thanks in advance

  select maxuser.userid,
         person.displayname,
         maxuser.status,
         sod_Report.severity,
         sod_Report.scenario,
         RTRIM (XMLAGG (XMLELEMENT (e, groupuser.groupname || ',')).EXTRACT ('//text()'),',') groupname
    from maxuser,
         person,
         groupuser,
         sod_report
   where    maxuser.userid=person.personid
        and groupuser.userid = maxuser.userid
        and sod_report.userid= maxuser.userid
group by maxuser.userid,
         person.displayname,
         maxuser.status,
         sod_Report.severity,
         sod_Report.scenario

Have a look at LISTAGG. Maybe it is not necessary to generate a XML out from a string and then extract string again from this XML. — Wernfried Domscheit
– Wernfried Domscheit, Commented Dec 2, 2016 at 6:55

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

The error message is self-descriptive.

19011, 00000, "Character string buffer too small"

Cause: The string result asked for is too big to return back

Action: Get the result as a lob instead

You need to use the DBMS_LOB package to retrieve an XMLTYPE data table. Here is an example which explains how to get the result as LOB. ORA-19011: Character string buffer too small

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 2, 2016 at 6:14

atokpas

3,3571 gold badge13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Murali Anan · Accepted Answer · 2018-07-31 11:33:31Z

1

we can use following function if we need to use more than 4000 characters -

select maxuser.userid,
         person.displayname,
         maxuser.status,
         sod_Report.severity,
         sod_Report.scenario,

         RTRIM (XMLAGG (XMLELEMENT (e, groupuser.groupname || ',')).EXTRACT ('//text()').getClobVal(),',') groupname

.....

answered Jul 31, 2018 at 11:33

Murali Anan

112 bronze badges

Collectives™ on Stack Overflow

Character string buffer too small error in Oracle

2 Answers 2

19011, 00000, "Character string buffer too small"

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

19011, 00000, "Character string buffer too small"

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related