Re: Cursor output to CSV

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 12 Jun 2006 10:16:47 -0700
Message-ID: <1150132607.258891.184840@f14g2000cwb.googlegroups.com>

Hi Helge, so far nobody has posted an implementation for your query so I thought I'd give it a go.

> The problem is: How do you output a cursor to csv? It's easy if you
> know the structure of the cursor ahead of time, but that presents
> maintenance problems. What's needed is a generic method.

With very little effort you will be able to wrap up what I've written below in a procedure passing a ref cursor etc. Note specifically that it meets your generic requirement - not one bit of the code references the columns ID, otherStuff1, otherStuff2, or otherStuff3 in table tblTest.

Good luck!

Mike

TESSELLA Michael.OS..._at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429

SQL> DESCRIBE tblTest

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 ID                                                 NUMBER
 OTHERSTUFF1                                        VARCHAR2(50)
 OTHERSTUFF2                                        DATE
 OTHERSTUFF3                                        NUMBER

SQL>
SQL> SELECT *
  2 FROM tblTest;

     ID OTHERSTUFF OTHERSTUF OTHERSTUFF3

------- ---------- --------- -----------
      1 One        11-JUN-06         1.1
      2 Two        10-JUN-06         2.2
      3 Three      09-JUN-06         3.3

SQL>

SQL>
SQL> DECLARE
  2 ctx DBMS_XMLGEN.ctxHandle;
  3 c1 SYS_REFCURSOR;
  4 csv VARCHAR2(1000);
  5 BEGIN
  6 OPEN c1 FOR

  7      SELECT *
  8        FROM tblTest
  9          WHERE id=1;

 10 ctx := DBMS_XMLGEN.NEWCONTEXT(c1);  11 SELECT XMLTransform(XMLTYPE(DBMS_XMLGEN.GETXML(ctx)),  12 XMLType('<?xml version="1.0"?>
 13 <xsl:stylesheet version="1.0"
 14 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">  15 <xsl:output method="text" encoding="iso-8859-1"/>  16 <xsl:template match="/ROWSET/ROW">
 17 <xsl:for-each select="child::node()">

 18     <xsl:value-of select="."/>
 19     <xsl:if test="position()!=last()">,</xsl:if>
 20    </xsl:for-each>

 21 </xsl:template>
 22 </xsl:stylesheet>')).getStringVal()  23 INTO csv
 24 FROM DUAL;
 25 DBMS_OUTPUT.PUT_LINE(csv);
 26 DBMS_XMLGEN.CLOSECONTEXT(ctx);
 27 CLOSE c1;
 28 END;
 29 /
1,One,11-JUN-06,1.1

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> SELECT *

  2 FROM V$VERSION; BANNER

---

Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production NLSRTL Version 10.1.0.4.0 - Production

SQL>
SQL> Received on Mon Jun 12 2006 - 12:16:47 CDT