Oracle csv exportingin UTF-8, with quotes escaping and optional headers

csv.sql

CREATE OR REPLACE PACKAGE csv AS
-- --------------------------------------------------------------------------
-- Name         : http://www.oracle-base.com/dba/miscellaneous/cvs.sql
-- Author       : DR Timothy S Hall
-- Description  : Initial setup:
--                CREATE OR REPLACE DIRECTORY CSV_DIR AS '/tmp';
--                GRANT WRITE ON DIRECTORY CSV_DIR TO scott;
--                Usage:
--                ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
--                ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SSXFF';
--                ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SSXFF TZR';
--
--                EXEC csv.generate('CSV_DIR', 'generate.csv', false, 'SELECT * FROM emp');
--
--                See http://www.oracle-base.com/articles/9i/GeneratingCSVFiles.php
-- Requirements : UTL_FILE, DBMS_SQL
-- Limitations  : printable data types, field size less than 16k
-- Ammedments   :
--   When         Who       What
--   ===========  ========  =================================================
--   21-FEB-2013  A. Ratto  Escape special chars, add parameter for header, 
--                          use UTF-8 output
--   14-MAY-2005  Tim Hall  Initial Creation
-- --------------------------------------------------------------------------

PROCEDURE generate (p_dir     IN  VARCHAR2,
                    p_file    IN  VARCHAR2,
                    p_header  IN  BOOLEAN,
                    p_query   IN  VARCHAR2);
END csv;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY csv AS
g_sep         VARCHAR2(1)  := ',';
g_eol         VARCHAR2(3)  := chr(10);
g_delim       VARCHAR2(1)  := '"';

PROCEDURE generate (p_dir     IN  VARCHAR2,
                    p_file    IN  VARCHAR2,
                    p_header  IN  BOOLEAN,
                    p_query   IN  VARCHAR2) AS

  l_cursor        PLS_INTEGER;
  l_rows          PLS_INTEGER;
  l_col_cnt       PLS_INTEGER;
  l_desc_tab      DBMS_SQL.desc_tab;
  l_buffer        VARCHAR2(32767);
  l_file          UTL_FILE.file_type;
  l_escaped_delim VARCHAR2(6);

BEGIN
  l_escaped_delim := g_delim || g_delim;

  l_cursor := DBMS_SQL.open_cursor;
  DBMS_SQL.parse(l_cursor, p_query, DBMS_SQL.native);

  DBMS_SQL.describe_columns(l_cursor, l_col_cnt, l_desc_tab);
  FOR i IN 1 .. l_col_cnt LOOP
    DBMS_SQL.define_column(l_cursor, i, l_buffer, 32767 );
  END LOOP;

  l_rows := DBMS_SQL.execute(l_cursor);

  l_file := UTL_FILE.fopen_nchar(p_dir, p_file, 'w');

  -- Output the column names.
  IF p_header THEN
    FOR i IN 1 .. l_col_cnt LOOP
      IF i > 1 THEN
        UTL_FILE.put_nchar(l_file, g_sep);
      END IF;
      UTL_FILE.put_nchar(l_file, g_delim);
      UTL_FILE.put_nchar(l_file, replace(l_desc_tab(i).col_name, g_delim, 
          l_escaped_delim ));
      UTL_FILE.put_nchar(l_file, g_delim);
      UTL_FILE.fflush(l_file);
    END LOOP;
    UTL_FILE.put_nchar(l_file, g_eol);
  END IF;

  -- Output the data.
  LOOP
    EXIT WHEN DBMS_SQL.fetch_rows(l_cursor) = 0;

    FOR i IN 1 .. l_col_cnt LOOP
      IF i > 1 THEN
        UTL_FILE.put_nchar(l_file, g_sep);
      END IF;

      DBMS_SQL.COLUMN_VALUE(l_cursor, i, l_buffer);
      UTL_FILE.put_nchar(l_file, g_delim);
      UTL_FILE.put_nchar(l_file, replace( l_buffer, g_delim, l_escaped_delim));
      UTL_FILE.put_nchar(l_file, g_delim);
      UTL_FILE.fflush(l_file);
    END LOOP;
    UTL_FILE.put_nchar(l_file, g_eol);
  END LOOP;

  UTL_FILE.fclose(l_file);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    IF DBMS_SQL.is_open(l_cursor) THEN
      DBMS_SQL.close_cursor(l_cursor);
    END IF;
    RAISE;
END generate;

END csv;
/
SHOW ERRORS