Link to home
Start Free TrialLog in
Avatar of Jblue R
Jblue R

asked on

listagg fail -string too long (4000) need to convert to XML

This code is a small portion of a view creation that develops a perfect answer except that one track_thing_id/thing_id combination exceeds the 4000 byte listagg limitation.  Because that one combination exceeds the 4000 character/byte limitation of listagg, I believe I need an XML conversion.  

    left outer join
           (  select mi8.track_thing_id,
                     listagg (
                            nvl (p.cipher, '')
                         || '-'
                         || mip.kellogg_nur
                         || '-'
                         || nvl (pls.situation, 'Nsitn'),
                         ', ')
                         within group (order by p.cipher)
                         as "thing/sit"
                from ml_track_thing mi8
                     join ml_track_thing_stuff mip
                         on mi8.track_thing_id = mip.track_thing_id
                     left outer join stuffstuff p on p.id = mip.endpr_id
                     left outer join lic_endpr_permit pl
                         on (    pl.endpr_id = mip.endpr_id
                             and pl.house_id = mi8.place_id)
                     left outer join lic_endpr_permit_situation pls
                         on pl.permit_situation_id = pls.id
            group by mi8.track_thing_id
            order by mi8.track_thing_id) spcl

Open in new window


So what I need from the above code is the XML equivalent..    and I am hoping the above is sufficient  for a resident expert to return the needed SQL..  And would gladly consider any alternative solution to the listagg limitation.

All pointers and suggestions appreciated ..  I have tested, and yes, there is only one combination (out of a large number) that come anywhere close to the 4000 byte limitation, and it clearly does exceed.. It is entirely possible that future data entry could cause additional  track_thing_id/thing_id combinations to exceed the limitation.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Here is the XML CLOB trick to exceed the 4000 character limit:
https://www.experts-exchange.com/questions/24914739/In-Oracle-how-can-I-concatenate-multiple-results-returned-from-select-statement-into-a-comma-separated-string.html?anchorAnswerId=25864822#a25864822

If you would like a copy/paste solution that wouldn't be a complete guess, please post sample data with the expected results.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jblue R

ASKER

@slightwv-- thank you for your post. I have to keep all the aliases intact to get it back in to the view statement. I am trying to work from yours back in to reality.  Thank you !
Avatar of Jblue R

ASKER

Got it!! Perfect.. thank you. I have the view.. the view is returning the solution field as HUGECLOB, which may become another issue...for another day...  .. You immediately returned the exact answer for which I searched..

Thank you once more.