I've been expanding on the dynamic pivot found on the apex forum using xmltransform, by @"fac586"
I've run into issues where the MIXED_DESC column in the example below heads into 4000+ character territory.
If I don't restrict the listagg with the having, it raises
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4440, maximum: 4000)
Trouble is this contrived sample doesn't raise an error, it just caps at 3909
with thedata as (
-- this is where my real query would actually live
select 'A'||substr(rownum,length(rownum)) status, 'SCOTT' login_id
,(rownum*50) multiplier
, lpad('x',(350+(rownum*50)),'x') mixed_Desc
from dual
connect by level <= 35)
,listagged as (
-- in the example the mixed_desc caps at 3800
select status, login_id, cast(replace(dbms_lob.substr(listagg_clob(mixed_desc),3800,1),',','') as varchar2(3800)) mixed_desc
from thedata
group by status, login_id
-- without this in my real case I get error: having length(cast(replace(dbms_lob.substr(listagg_clob(mixed_desc),4000,1),',','') as varchar2(4000))) < 500
)
,rep_pivot as (
select
status
, login_id_xml
from
listagged
pivot xml
(max(mixed_desc) mixed_desc for login_id in (select distinct login_id from thedata)))
select
status
,length(login_id_xml)
,utl_i18n.unescape_reference( xmlserialize(
content
xmltransform(
login_id_xml
, xmltype(q'{
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" />
<xsl:template match="/PivotSet">
<xsl:for-each select="item">
<td>
<xsl:attribute name="headers">
<xsl:value-of select="column[@name='LOGIN_ID']" />
</xsl:attribute>
<xsl:value-of select="column[@name='MIXED_DESC']" />
</td>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>}')))) matrix
from rep_pivot
order by 2 desc;
In my actual example, if I replace the final transform with this, the greatest length is 4170, which I thought was odd.
select status, login_id_xml, dbms_lob.getlength(xmltype.getclobval(login_id_xml))from rep_pivot
Any tips on what I could do, look out for, try?