SQL Error: ORA-01489: result of string concatenation is too long

Question

I am working on the query below:

select ip.intake_id,
       ip.estimated_years,
       ip.gender_code,
       LISTAGG(ip.race_code, ',') WITHIN GROUP (ORDER BY ip.race_code) as race_code,
       eth.ethnicity_code, 
       i.living_arrangements,
       p.dep_actv_military_flag,
       LISTAGG(ale.allegation_super_type_code) WITHIN GROUP (ORDER BY ale.allegation_super_type_code) as maltreatment_type_code,
       LISTAGG(ale.initial_report_disp_code) WITHIN GROUP (ORDER BY ale.initial_report_disp_code) as maltreatment_dispo_lvl,
       ip.deceased_flag,
       LISTAGG(ch.characteristic_code, ',') WITHIN GROUP (ORDER BY ch.characteristic_code) as chara_codes,
       LISTAGG(ich.intake_characteristic_code, ',') WITHIN GROUP (ORDER BY ich.intake_characteristic_code) as intake_chara_codes,
       pe.removed_date,cm.petition_submitted_flag,cm.created_date,atr.person_id
from intake i inner join intake_participant ip on i.intake_id = ip.intake_id
       left outer join reporter r ON i.intake_id=r.intake_id
       left outer join ethnicity eth on eth.person_id = ip.person_id
       left outer join person p on p.person_id  = ip.person_id
       left outer join allegation ale on ale.intake_id = i.intake_id
       left outer join characteristic ch on ch.person_id = ip.person_id
       left outer join intake_characteristic ich on ich.intake_id = i.intake_id
       left outer join placement_episode pe on pe.child_id = ip.person_id
       left outer join complaint cm on cm.petitioner_id = ip.person_id
       left outer join attorney atr on atr.person_id = ip.person_id
       left outer join intake_participant_role apr on apr.intake_participant_id = ip.intake_participant_id
group by ip.intake_id,ip.estimated_years,ip.gender_code,eth.ethnicity_code,i.living_arrangements,p.dep_actv_military_flag,
    ip.deceased_flag,pe.removed_date,cm.petition_submitted_flag,cm.created_date,atr.person_id

when I am running this query I am getting following error message:

Error report:
SQL Error: ORA-01489: result of string concatenation is too long
01489. 00000 -  "result of string concatenation is too long"
*Cause:    String concatenation result is more than the maximum size.
*Action:   Make sure that the result is less than the maximum size.  

But when I remove the line :

left outer join reporter r ON i.intake_id=r.intake_id 

from my query then it executes without any error message. The working query is given below:

select ip.intake_id,
       ip.estimated_years,
       ip.gender_code,
       LISTAGG(ip.race_code, ',') WITHIN GROUP (ORDER BY ip.race_code) as race_code,
       eth.ethnicity_code, 
       i.living_arrangements,
       p.dep_actv_military_flag,
       LISTAGG(ale.allegation_super_type_code) WITHIN GROUP (ORDER BY ale.allegation_super_type_code) as maltreatment_type_code,
       LISTAGG(ale.initial_report_disp_code) WITHIN GROUP (ORDER BY ale.initial_report_disp_code) as maltreatment_dispo_lvl,
       ip.deceased_flag,
       LISTAGG(ch.characteristic_code, ',') WITHIN GROUP (ORDER BY ch.characteristic_code) as chara_codes,
       LISTAGG(ich.intake_characteristic_code, ',') WITHIN GROUP (ORDER BY ich.intake_characteristic_code) as intake_chara_codes,
       pe.removed_date,cm.petition_submitted_flag,cm.created_date,atr.person_id
from intake i inner join intake_participant ip on i.intake_id = ip.intake_id
       left outer join ethnicity eth on eth.person_id = ip.person_id
       left outer join person p on p.person_id  = ip.person_id
       left outer join allegation ale on ale.intake_id = i.intake_id
       left outer join characteristic ch on ch.person_id = ip.person_id
       left outer join intake_characteristic ich on ich.intake_id = i.intake_id
       left outer join placement_episode pe on pe.child_id = ip.person_id
       left outer join complaint cm on cm.petitioner_id = ip.person_id
       left outer join attorney atr on atr.person_id = ip.person_id
       left outer join intake_participant_role apr on ipr.intake_participant_id = ip.intake_participant_id
group by ip.intake_id,ip.estimated_years,ip.gender_code,eth.ethnicity_code,i.living_arrangements,p.dep_actv_military_flag,
    ip.deceased_flag,pe.removed_date,cm.petition_submitted_flag,cm.created_date,atr.person_id

I am not sure why this error occured. Can someone help me to figure out the problem? I got same questions from these links link1 and link2, but I didn't get the solution to my question from these links.

Answer 1

Let's take a different approach: just like when you're aggregating sums and joining to other tables, sometimes you have to materialize the results before you additional one-to-many join so your aggregation isn't artificially inflated due to multiple records in joining tables. This approach is assuming you don't need the repetition that is occurring due to the additional table joins however.

I think by creating the inline view the results of the race_Code will fit within size limits. This could be done using a CTE as well. Simply put, provided you don't need the record duplication, you may need to materialize the listAgg results individually first then join them back in. If you have other problems with the other listAggs, you may want to create multiple CTE's and then join them all back together in the end. This approach simply uses a inline view.

select B.intake_id,
       B.estimated_years,
       B.gender_code,
       B.race_code,
       eth.ethnicity_code, 
       B.living_arrangements,
       p.dep_actv_military_flag,
       LISTAGG(ale.allegation_super_type_code) WITHIN GROUP (ORDER BY ale.allegation_super_type_code) as maltreatment_type_code,
       LISTAGG(ale.initial_report_disp_code) WITHIN GROUP (ORDER BY ale.initial_report_disp_code) as maltreatment_dispo_lvl,
       B.deceased_flag,
       LISTAGG(ch.characteristic_code, ',') WITHIN GROUP (ORDER BY ch.characteristic_code) as chara_codes,
       LISTAGG(ich.intake_characteristic_code, ',') WITHIN GROUP (ORDER BY ich.intake_characteristic_code) as intake_chara_codes,
       pe.removed_date,cm.petition_submitted_flag,cm.created_date,atr.person_id
from (SELECT ip.intake_id,
       ip.estimated_years,
       ip.gender_code,
       ip.deceased_flag,
       ip.person_id,
       ip.intake_participant_id,
       LISTAGG(ip.race_code, ',') WITHIN GROUP (ORDER BY ip.race_code) as race_code, i.living_arrangements 
      FROM intake i 
      INNER JOIN intake_participant ip on i.intake_id = ip.intake_id
      GROUP BY ip.intake_id, ip.estimated_years, ip.gender_code, 
               i.living_arrangements, ip.deceased_flag, ip.person_id,                
               ip.intake_participant_id) B
left outer join ethnicity eth on eth.person_id = B.person_id
left outer join person p on p.person_id  = B.person_id
left outer join allegation ale on ale.intake_id = B.intake_id
left outer join characteristic ch on ch.person_id = B.person_id
left outer join intake_characteristic ich on ich.intake_id = i.intake_id
left outer join placement_episode pe on pe.child_id = B.person_id
left outer join complaint cm on cm.petitioner_id = B.person_id
left outer join attorney atr on atr.person_id = B.person_id
left outer join intake_participant_role apr on ipr.intake_participant_id = B.intake_participant_id
GROUP BY  ip.intake_id,ip.estimated_years,ip.gender_code, eth.ethnicity_code,i.living_arrangements,p.dep_actv_military_flag, ip.deceased_flag,pe.removed_date,cm.petition_submitted_flag, cm.created_date,atr.person_id

Answer 2

The join to table "reporter" is probably increasing the record count (this could only be the case if column "intake_id" is not a unique key of "reporter"). By increasing the record count, you are generating more strings that LISTAGG must concatenate together within each group. If the total length of concatenated strings exceeds 4000 bytes, LISTAGG will fail with the error you see.