Sunday, June 12, 2011

Return any table/query as CSV data - dynamically (Using XSLT)

Use XSLT in PL/SQL to format data returned from dbms_xmlgen.getxml as CSV (or anything else).

This method is MUCH faster than a previous solution here, using DBMS_SQL.

Example Call:

SELECT GET_CSV_FROM_CURSOR(CURSOR(select ROWNUM, a.* from all_objects a WHERE ROWNUM < 10000)) FROM dual;






CREATE OR REPLACE FUNCTION GET_CSV_FROM_CURSOR(IN_SQL IN SYS_REFCURSOR) 
RETURN CLOB AS
OUT_CLOB CLOB;
NCTX NUMBER;
STSH XMLTYPE;
res_xml xmltype;

BEGIN

/*Prepare SQL statment */
NCTX := DBMS_XMLGEN.NEWCONTEXT(IN_SQL);

/*I want null columns returned! */
DBMS_XMLGEN.USENULLATTRIBUTEINDICATOR(NCTX,TRUE);

/*Stylesheet - tells processor how to format the resultant output */
STSH := XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"  xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
<xsl:output method="text" indent="no" />
<xsl:template match="/">
<xsl:for-each select="//ROWSET/ROW">
<xsl:for-each select="*">
<xsl:choose>
<xsl:when test="position() =  last()">
<xsl:text>"</xsl:text><xsl:value-of select="."/><xsl:text>"
</xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:text>"</xsl:text><xsl:value-of select="."/><xsl:text>"</xsl:text><xsl:text>,</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>');

/* Get your data, transform then get clob value ('Transform' returns an xmltype) 
Also, if you're working with text that have ampersands, we replace for the parse, then readd after*/
res_xml :=  xmltype(replace(DBMS_XMLGEN.GETXML(NCTX),'&', 
'&'||'amp;')).TRANSFORM(STSH);
out_clob := replace(res_xml.getclobval(), '&'||'amp;', '&');

/* No longer need the xml context stuff */
DBMS_XMLGEN.CLOSECONTEXT(NCTX);

RETURN OUT_CLOB;

END;

No comments:

Post a Comment