CODEWITHSUNDEEP
oracle-database
max092012
max092012

Reputation: 407

Character string buffer too small error in Oracle

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

Upvotes: 0

Views: 6832

Answers (2)

Murali Anan
Murali Anan

Reputation: 11

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

.....

Upvotes: 1

atokpas
atokpas

Reputation: 3351

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

Upvotes: 1

Related Questions