dynamic csv file generation using quey or view in oracle.

FUNCTION generate_csv_file (
      p_query                    IN       VARCHAR2
,     p_dir                      IN       VARCHAR2
,     p_filename                 IN       VARCHAR2
,     p_separator                IN       VARCHAR2
,     p_headers                  IN       boolean default false
,     p_trailing_separator       IN       boolean default false
,     p_max_linesize             IN       NUMBER DEFAULT 32000
,     p_mode                     IN       VARCHAR2 DEFAULT 'w' )
      RETURN NUMBER
   IS
      l_output                      utl_file.file_type;
      l_thecursor                   INTEGER DEFAULT dbms_sql.open_cursor;
      l_columnvalue                 VARCHAR2 ( 10000 );
      l_status                      INTEGER;
      l_colcnt                      NUMBER DEFAULT 0;
      l_cnt                         NUMBER DEFAULT 0;
      l_separator                   VARCHAR2 ( 10 ) DEFAULT '';
      l_line                        LONG;
      l_desctbl                     dbms_sql.desc_tab;
      v_sqlerrm                     VARCHAR2 ( 32000 );
      l_mode                        CHAR ( 1 ) := 'w';
   BEGIN
      IF p_mode NOT IN ( 'w', 'a' )
      THEN
         l_mode := 'w';
      ELSE
         l_mode := p_mode;
      END IF;

      l_output := utl_file.fopen ( p_dir
,                                  p_filename
,                                  l_mode
,                                  p_max_linesize );
      dbms_sql.parse ( l_thecursor
,                      p_query
,                      dbms_sql.native );
      dbms_sql.describe_columns ( l_thecursor
,                                 l_colcnt
,                                 l_desctbl );

      FOR i IN 1 .. l_colcnt
      LOOP
         dbms_sql.define_column ( l_thecursor
,                                 i
,                                 l_columnvalue
,                                 4000 );

         IF ( l_desctbl ( i ).col_type = 2 )                   /* number type */
         THEN
            l_desctbl ( i ).col_max_len := l_desctbl ( i ).col_precision + 2;
         ELSIF ( l_desctbl ( i ).col_type = 12 )                 /* date type */
         THEN
/* length of my date format */
            l_desctbl ( i ).col_max_len := 20;
         ELSIF ( l_desctbl ( i ).col_type = 8 )                  /* LONG type */
         THEN
            l_desctbl ( i ).col_max_len := 2000;
         END IF;
       
----  DBMS_OUTPUT.PUT_LINE(l_desctbl ( i ).col_type);

         IF  p_headers
         THEN
            utl_file.put ( l_output, l_separator || l_desctbl ( i ).col_name );
            l_separator := p_separator;
         END IF;
      END LOOP;

      IF p_trailing_separator
      THEN
         utl_file.put ( l_output, l_separator );
      END IF;

      IF p_headers
      THEN
         utl_file.new_line ( l_output );
      END IF;

      l_status := dbms_sql.EXECUTE ( l_thecursor );

      LOOP
         EXIT WHEN ( dbms_sql.fetch_rows ( l_thecursor ) <= 0 );
         l_line := NULL;
         l_separator := '';

         FOR i IN 1 .. l_colcnt
         LOOP
            dbms_sql.COLUMN_VALUE ( l_thecursor
,                                   i
,                                   l_columnvalue );

            IF NVL ( INSTR ( l_columnvalue, ',' ), 0 ) = 0
            THEN
               NULL;
            ELSE
               l_columnvalue := '"' || l_columnvalue || '"';
            END IF;

            utl_file.put ( l_output, l_separator || l_columnvalue );
            l_separator := p_separator;
         END LOOP;

         IF p_trailing_separator
         THEN
            utl_file.put ( l_output, l_separator );
         END IF;

         utl_file.new_line ( l_output );
         l_cnt := l_cnt + 1;
      END LOOP;

      dbms_sql.close_cursor ( l_thecursor );
      utl_file.fclose ( l_output );
      RETURN l_cnt;
     
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         --  DBMS_OUTPUT.put_line ( 'NO_DATA_FOUND' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
      WHEN utl_file.invalid_path
      THEN
         --  DBMS_OUTPUT.put_line ( 'UTL_FILE.INVALID_PATH' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
      WHEN utl_file.read_error
      THEN
         --  DBMS_OUTPUT.put_line ( 'UTL_FILE.READ_ERROR' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
      WHEN utl_file.write_error
      THEN
         --  DBMS_OUTPUT.put_line ( 'UTL_FILE.WRITE_ERROR' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
      WHEN utl_file.invalid_mode
      THEN
         --  DBMS_OUTPUT.put_line ( 'UTL_FILE.INVALID_MODE' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
      WHEN utl_file.invalid_filehandle
      THEN
         --  DBMS_OUTPUT.put_line ( 'UTL_FILE.INVALID_FILEHANDLE' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
      WHEN utl_file.invalid_operation
      THEN
         --  DBMS_OUTPUT.put_line ( 'UTL_FILE.INVALID_OPERATION' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
      WHEN utl_file.internal_error
      THEN
         --  DBMS_OUTPUT.put_line ( 'UTL_FILE.INTERNAL_ERROR' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
      WHEN utl_file.invalid_maxlinesize
      THEN
         --  DBMS_OUTPUT.put_line ( 'UTL_FILE.INVALID_MAXLINESIZE' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
      WHEN VALUE_ERROR
      THEN
         --  DBMS_OUTPUT.put_line ( 'UTL_FILE.VALUE_ERROR' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
      WHEN OTHERS
      THEN
         --hum_do.default_exception ( 'ERROR in dump_csv : ' );
         utl_file.fclose ( l_output );
         RETURN l_cnt;
   END generate_csv_file;

xml dom functions explanation with examples in plsql.

xml functions:

======================

DBMS_XMLGEN.newContext(query);

---Create new Context for the Query

example:

=====================

 v_ctx                     dbms_xmlgen.ctxHandle;

 v_mem_xml                 XMLTYPE;

v_mem_select VARCHAR2(4000):='select EFFECTIVE_TO_DATE,INCEPTION_DATE,MEM_STATUS,EFFECTIVE_FROM_DATE  FROM vw_rule_member WHERE member_seq_id =17541428';

v_ctx := dbms_xmlgen.newContext(v_mem_select);

*****************************************************************************************************************

dbms_xmlgen.setRowSetTag(v_ctx,'fielddata');

--it will set rowset as fielddata

example:

===================

before:

--------

<ROWSET>

 <ROW>

  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

  <MEM_STATUS>POA</MEM_STATUS>

  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

 </ROW>

</ROWSET>

declare

 v_ctx                     dbms_xmlgen.ctxHandle;

 v_mem_xml                 XMLTYPE;

begin

v_mem_select VARCHAR2(4000):='select EFFECTIVE_TO_DATE,INCEPTION_DATE,MEM_STATUS,EFFECTIVE_FROM_DATE  FROM vw_rule_member WHERE member_seq_id =17541428';

v_ctx := dbms_xmlgen.newContext(v_mem_select);

dbms_xmlgen.setRowSetTag(v_ctx,'fielddata');

  v_mem_xml := DBMS_XMLGEN.GetXMLTYPE(v_ctx);

end;

after:

---------

<fielddata>

 <ROW>

  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

  <MEM_STATUS>POA</MEM_STATUS>

  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

 </ROW>

</fielddata>

***************************************************************************************************

  dbms_xmlgen.setRowTag(v_ctx,'MEMBER');

--it will set row as MEMBER

example:

======================

before:

---------

<ROWSET>

 <ROW>

  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

  <MEM_STATUS>POA</MEM_STATUS>

  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

 </ROW>

</ROWSET>

declare

 v_ctx                     dbms_xmlgen.ctxHandle;

 v_mem_xml                 XMLTYPE;

begin

v_mem_select VARCHAR2(4000):='select EFFECTIVE_TO_DATE,INCEPTION_DATE,MEM_STATUS,EFFECTIVE_FROM_DATE  FROM vw_rule_member WHERE member_seq_id =17541428';

v_ctx := dbms_xmlgen.newContext(v_mem_select);

dbms_xmlgen.setRowTag(v_ctx,'MEMBER');

  v_mem_xml := DBMS_XMLGEN.GetXMLTYPE(v_ctx);

end;

after:

----------

<ROWSET>

 <MEMBER>

  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

  <MEM_STATUS>POA</MEM_STATUS>

  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

 </MEMBER>

</ROWSET>

*****************************************************************************************************************

dbms_xmldom.importNode(doc ,importednode ,'true/false');

--If set to false, only the node itself is imported, if set to true, all child nodes (descendants)

-- are also imported

IT WILL IMPORT NODE VALUE TO DOM DOCUMENT  THEN WE WILL APPEND THE NODE DATA TO DOM DOCUMENT

v_req_root_node := dbms_xmldom.importNode(v_mem_doc,v_req_root_node,TRUE);

   v_mem_root_node := dbms_xmldom.appendChild(v_mem_root_node,v_req_root_node);

   v_field_xml := dbms_xmldom.getxmltype(v_mem_doc);

WITHOUT USING IMPORT NODE FUNCTION YOU CANNOT APPEND ONE NODE TO ANOTHER NODE.

example:

===============================

before:

----------------

<fielddata>

  <MEMBER>

    <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

    <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

    <MEM_STATUS>POA</MEM_STATUS>

    <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

  </MEMBER>

</fielddata>

declare

 v_mem_doc                 DBMS_XMLDOM.DOMDocument;

    v_req_root_node           DBMS_XMLDOM.DOMNode;

    v_mem_root_node           DBMS_XMLDOM.DOMNode;

v_dom_ele dbms_xmldom.DOMElement;

 v_mem_xml                 XMLTYPE;

v_buff  varchar2(20000);

begin

v_mem_xml    :=xmltype('<fielddata>

 <MEMBER>

  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

  <MEM_STATUS>POA</MEM_STATUS>

  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

 </MEMBER>

</fielddata>

');

 v_mem_doc := DBMS_XMLDOM.newDOMDocument(v_mem_xml);

   v_mem_root_node := DBMS_XMLDOM.makeNode(DBMS_XMLDOM.getDocumentElement((v_mem_doc)));

dbms_xmldom.writeToBuffer(v_mem_root_node,v_buff);

dbms_output.put_line(v_buff);

 

   v_mem_root_node := dbms_xmldom.importNode(v_mem_doc,v_mem_root_node,true);

dbms_xmldom.writeToBuffer(v_mem_root_node,v_buff);

dbms_output.put_line(v_buff);

end;

after:

-------------

<fielddata>

  <MEMBER>

    <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

    <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

    <MEM_STATUS>POA</MEM_STATUS>

    <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

  </MEMBER>

</fielddata>

before:

----------------

<fielddata>

  <MEMBER>

    <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

    <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

    <MEM_STATUS>POA</MEM_STATUS>

    <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

  </MEMBER>

</fielddata>

declare

 v_mem_doc                 DBMS_XMLDOM.DOMDocument;

    v_req_root_node           DBMS_XMLDOM.DOMNode;

    v_mem_root_node           DBMS_XMLDOM.DOMNode;

v_dom_ele dbms_xmldom.DOMElement;

 v_mem_xml                 XMLTYPE;

v_buff  varchar2(20000);

begin

v_mem_xml    :=xmltype('<fielddata>

 <MEMBER>

  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

  <MEM_STATUS>POA</MEM_STATUS>

  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

 </MEMBER>

</fielddata>

');

 v_mem_doc := DBMS_XMLDOM.newDOMDocument(v_mem_xml);

   v_mem_root_node := DBMS_XMLDOM.makeNode(DBMS_XMLDOM.getDocumentElement((v_mem_doc)));

dbms_xmldom.writeToBuffer(v_mem_root_node,v_buff);

dbms_output.put_line(v_buff);

 

   v_mem_root_node := dbms_xmldom.importNode(v_mem_doc,v_mem_root_node,false);

dbms_xmldom.writeToBuffer(v_mem_root_node,v_buff);

dbms_output.put_line(v_buff);

end;

after:

-------------

<fielddata/>

***************************************************************************

DBMS_XMLDOM.getDocumentElement;

--it will return rool element for xml

Example:

---------------\

Before:

<fielddata>

 <MEMBER>

  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

  <MEM_STATUS>POA</MEM_STATUS>

  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

 </MEMBER>

</fielddata>

declare

 v_mem_doc                 DBMS_XMLDOM.DOMDocument;

    v_req_root_node           DBMS_XMLDOM.DOMNode;

    v_mem_root_node           DBMS_XMLDOM.DOMNode;

v_dom_ele dbms_xmldom.DOMElement;

 v_mem_xml                 XMLTYPE;

v_buff  varchar2(20000);

begin

v_mem_xml    :=xmltype('<fielddata>

 <MEMBER>

  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

  <MEM_STATUS>POA</MEM_STATUS>

  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

 </MEMBER>

</fielddata>

');

 v_mem_doc := DBMS_XMLDOM.newDOMDocument(v_mem_xml);

v_dom_ele:=DBMS_XMLDOM.getDocumentElement((v_mem_doc));

end;

   v_mem_root_node := DBMS_XMLDOM.makeNode(DBMS_XMLDOM.getDocumentElement((v_mem_doc)));

dbms_xmldom.writeToBuffer(v_mem_root_node,v_buff);

dbms_output.put_line(v_buff);

after:

<fielddata>

 <MEMBER>

  <EFFECTIVE_TO_DATE>31/03/2014</EFFECTIVE_TO_DATE>

  <INCEPTION_DATE>01/04/2013</INCEPTION_DATE>

  <MEM_STATUS>POA</MEM_STATUS>

  <EFFECTIVE_FROM_DATE>01/04/2013</EFFECTIVE_FROM_DATE>

 </MEMBER>

</fielddata>