Is there an Oracle PL/SQL command that automatically creates a .csv or .xml file from a query within a script?

Question

I've been working with constant & repetitive SQL extracts & I would like to develop a script that automatically downloads in Excel (or csv) several searches, which would depend on a "while" statement with parameters.

Usually I would run my SQL query & manually download it with Toad's options, but I would like to make this process less dependent of me.

It would be something like:

DECLARE
     {parameters definition};
BEGIN
     FOR T IN ({table with parameters})
          LOOP
               {Run a query that gets extracted to a .csv file upon completion}
               SELECT * FROM {table} WHERE T.{parameters};

          END LOOP;

END;
/

I expect the script to run the query several times (because of the loop) & download a file from each after each run.

Do you know if it's posible & how it could be achieved?

Answer 1

You can use "xmlelement" in your query. It helps you to see data as XML output. Actually, this way is not creating any .xml files but it returns XML format. After that, you can export it.

Here is an example;

select xmlelement("Item"
                  ,xmlelement("action_type"
                             ,'I')
                  ,xmlelement("dc_id"
                             ,cmn.doc_id)
                  ,xmlelement("owner"
                             ,cmn.owner)
                  ,xmlelement("name"
                             ,cmn.name)) 
from members cmn

Hope it helps.

Answer 2

Below is my slightly modified code to create HTML from any arbitrary cursor. I originally got from StackOverflow, I apologize that I don't have the link to the original author who deserves the credit. You will have to build your own routine to write this to the file system. Check out UTL_FILE.

   -- ************************************************************************
    --  To HTML
    --  Purpose:
    --    Get HTML for results of cursor
    --  Arguments:
    --    p_cursor - cursor for which we want results
    --    p_bgcolor - color to use for backgrong
    --    p_size size of text within the table (goes up to 7 I believe)
    --  Returns:
    --    clob containing HTML
    --  Notes:
    --    I wish I could claim credit for this brilliant piece of code, but it
    --    came from Stack Overflow. Original function name was fncRefCursor2HTML
    -- ************************************************************************

FUNCTION to_html( p_cursor    IN SYS_REFCURSOR
                , p_bgcolor   IN VARCHAR2 DEFAULT t.defaultcolor
                , p_size      IN INTEGER DEFAULT 2 )
    RETURN CLOB IS
    l_routine   CONSTANT VARCHAR2( 10 ) := 'to_html';

    l_retval             CLOB;

    l_htmloutput         XMLTYPE;

    l_xsl                CLOB;

    l_xmldata            XMLTYPE;

    l_context            DBMS_XMLGEN.ctxhandle;

    no_results           EXCEPTION;

    PRAGMA EXCEPTION_INIT ( no_results, -30625 );

    FUNCTION getoption( p_option IN VARCHAR2, p_value IN VARCHAR2 )
        RETURN VARCHAR2 AS
        l_routine   CONSTANT t.oracleobj_t := 'getoption';
        l_ret                t.smallcmd_t;
    BEGIN
        IF p_value IS NULL
        THEN
            RETURN NULL;
        END IF;

        IF p_option IS NULL
        THEN
            raise_application_error( -20001, 'p_option argument to getoption cannot be null' );
        END IF;

        l_ret   := ' ' || p_option || '="' || p_value || '" ';

        IF g_trace
        THEN
            trace( p_routine   => l_routine
                 , p_message   => labels( 'p_option'
                                        , p_option
                                        , 'p_value'
                                        , p_value
                                        , 'return value'
                                        , l_ret ) );
        END IF;

        RETURN l_ret;
    END getoption;
BEGIN
    -- get a handle on the ref cursor --

    l_context      := DBMS_XMLGEN.newcontext( p_cursor );

    -- setNullHandling to 1 (or 2) to allow null columns to be displayed --

    DBMS_XMLGEN.setnullhandling( l_context, 1 );

    -- create XML from ref cursor --

    l_xmldata      := DBMS_XMLGEN.getxmltype( l_context, DBMS_XMLGEN.none );

    -- this is a generic XSL for Oracle's default XML row and rowset tags --
    -- " " is a non-breaking space --

    l_xsl          :=
           '<?xml version="1.0" encoding="ISO-8859-1"?>
 <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html"/>
  <xsl:template match="/">
  <html>
   <body>
    <table border="1" '
        || getoption( 'size', p_size )
        || '>
      <tr'
        || getoption( 'bgcolor', p_bgcolor )
        || '>
       <xsl:for-each select="/ROWSET/ROW[1]/*">
        <th><xsl:value-of select="name()"/></th>
       </xsl:for-each>
      </tr>
      <xsl:for-each select="/ROWSET/*">
       <tr>
        <xsl:for-each select="./*">
         <td><xsl:value-of select="text()"/> </td>
        </xsl:for-each>
       </tr>
      </xsl:for-each>
    </table>
   </body>
  </html>
  </xsl:template>
 </xsl:stylesheet>';

    -- XSL transformation to convert XML to HTML --

    l_htmloutput   := l_xmldata.transform( xmltype( l_xsl ) );

    -- convert XMLType to Clob --

    l_retval       := l_htmloutput.getclobval( );

    RETURN l_retval;
EXCEPTION
    WHEN no_results
    THEN
        -- If the table does not return any rows, oracle raises
        -- ORA-30625: method dispatch on NULL SELF argument is disallowed
        -- Return an empty lob in this case
        DBMS_LOB.createtemporary( l_retval, FALSE );

        RETURN l_retval;
    WHEN OTHERS
    THEN
        cealogging.activity_log_maintenance_pkg.make_log_error_entry(
            p_owner         => 'CEAADMIN'
          , p_application   => $$plsql_unit
          , p_routine       => l_routine
          , p_message       =>
                   label( p_label => 'l_xsl', p_value => l_xsl )
                || label( p_label => 'p_bgcolor', p_value => p_bgcolor ) );

        RAISE;
END to_html;