How can I generate flat files of Comma-Separated Values (CSV) from Oracle tables?

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any 
REM specific industrial application nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (see address below) when 
REM you have comments, suggestions, and/or difficulties with this
REM packages functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM NAME: DH_FILE.SQL - PL/SQL code to create a package (DH_FILE)
REM       providing the following 5 procedures and 1 function:
REM        1) DH_FILE.FILE_OPEN    (<file#>,<path>,<file>,
REM                     <'W','R','A'>)
REM            [Write, Read, Append] respectively.
REM        2) DH_FILE.FILE_CLOSE    (<file#>)
REM            ...uses handle from FILE array.
REM        3) DH_FILE.FILE_PRT    (<file#>,<output string>)
REM            ...uses handle from FILE array.
REM        4) DH_FILE.FILE_STAGE    (<file#>,<output string>)
REM            ...uses handle from FILE array.
REM        5) DH_FILE.FILE_FLUSH    (<file#>)
REM            ...uses handle from FILE array.
REM        6) DH_FILE.READ_NEXT    (<file#>)
REM            ...RETURNs next line from file_handle
REM            ...uses handle from FILE array.
REM
REM    This package facilitates usage of the Oracle UTL_FILE
REM    package.  The UTL_FILE file operations include: 
REM    FOPEN, IS_OPEN, FCLOSE, FCLOSE_ALL, GET_LINE, PUT,
REM    NEW_LINE, PUT_LINE, PUTF, and FFLUSH 
REM
REM    VERY IMPORTANT NOTE!!!: For any write use of UTL_FILE,
REM    the instance must be prepared to write to O/S directories
REM    via the init.ora parameter,
REM    "UTL_FILE_DIR = <path><or "*", meaning "all directories">.
REM
REM AUTHOR: Dave Hunt, Co-Principal, DaSages
REM        dave@Dasages.com
REM         1-801-TRY-DAVE (1-801-879-3283).
REM
REM **************************************************************
REM Maintenance History:
REM
REM 19-MAR-97: Original Code
REM 22-DEC-98: Enhanced to allow for multiple file handling by number
REM **************************************************************
REM **************************************************************
REM DH_FILE Package Specification
REM **************************************************************
create or replace package dh_file is
   procedure FILE_OPEN     (which_file in binary_integer,
            path in varchar2,
            filename in varchar2,
            read_write_append in varchar2);
   procedure FILE_CLOSE (which_file in binary_integer);
   procedure FILE_PRT    (which_file in binary_integer,
            str_to_prt in varchar2);
   procedure FILE_STAGE (which_file in binary_integer,
            str_to_prt in varchar2);
   procedure FILE_FLUSH (which_file in binary_integer);
   function  READ_NEXT     (which_file in binary_integer) return varchar2;
   -- pragma restrict_references(read_next,WNDS);
end;
/
REM **************************************************************
REM DH_FILE Package Body
REM **************************************************************
create or replace package body dh_file is
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_OPEN 
--  **************************************************************
    type file_handle_stencil is table of utl_file.file_type
        index by binary_integer;
    file_handles file_handle_stencil;
    file_handle    utl_file.file_type;
    procedure FILE_OPEN     (which_file in binary_integer,
                path in varchar2,
                filename in varchar2,
                read_write_append in varchar2) is
    begin
        file_handles(which_file)
                :=    utl_file.fopen(
                    location => path,
                    filename => filename,
                    open_mode => read_write_append);
    end;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_CLOSE 
--  **************************************************************
    procedure FILE_CLOSE (which_file in binary_integer) is
    begin
        utl_file.fclose(file_handles(which_file));
    end FILE_CLOSE;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_PRT 
--  **************************************************************
    procedure FILE_PRT     (which_file in binary_integer,
                str_to_prt in varchar2) is 
    begin
        utl_file.put_line(file_handles(which_file), str_to_prt);
    end FILE_PRT;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_STAGE
--  **************************************************************
    procedure FILE_STAGE     (which_file in binary_integer,
                str_to_prt in varchar2) is
    begin
        utl_file.put (file_handles(which_file), str_to_prt);
    end FILE_STAGE;
--  **************************************************************
--  Packaged Global Procedure Definition: DH_FILE.FILE_FLUSH
--  **************************************************************
    procedure FILE_FLUSH     (which_file in binary_integer) is
    begin
        utl_file.fflush (file_handles(which_file));
    end FILE_FLUSH;
--  **************************************************************
--  Packaged Global Function Definition: DH_FILE.READ_NEXT 
--  **************************************************************
    function  READ_NEXT     (which_file in binary_integer)
                return varchar2 is
        hold_text    varchar(2000);
    begin
        utl_file.get_line (file_handles(which_file), hold_text);
        return hold_text;
    exception
        when no_data_found then
            return '<EOF>';
    end READ_NEXT;
end;
/
REM **************************************************************
REM End of Package: DH_FILE
REM **************************************************************

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any 
REM specific industrial application nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (see address below) when 
REM you have comments, suggestions, and/or difficulties with this
REM packages functions.
REM
REM AUTHOR: Dave Hunt, Co-Principal, DaSages
REM        dave@Dasages.com
REM         1-801-TRY-DAVE (1-801-879-3283)
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM Maintenance History:
REM
REM 26-FEB-2004: Original Code
REM **************************************************************
REM About this script/file (CSVMaker.sql):
REM
REM    This script prompts for a table name in the current schema,
REM    then generates a "temp.sql" PL/SQL script tailored to output
REM    Comma-Separated Values for the prompted table. The script
REM    ensures field-level character-string integrity by enclosing
REM    all data values in double quotes.
REM
REM    This script refers to (and thus depends upon) a "utl_file"
REM    facilitating package named "DH_FILE". That package must
REM    run successfully and reside in the schema that uses this
REM    script.
REM    
REM    You can simulate batch processing by copying and pasting
REM    at the "SQL>" prompt, invocations of the "CSVMaker" script
REM    followed by the table name you wish to CSV, for example:
REM    @CSVMaker
REM    s_region
REM    @CSVMaker
REM    s_dept
REM    et cetera
REM
REM **************************************************************
accept tabname prompt "Provide the tablename for which you want a .csv file: "
declare
    path        varchar2(2000)    := 'd:\dhunt\sqldba\';
    /* You must change "path", above, to a valid output path for your system */
    filename    varchar2(100)    := 'temp.sql';
    t        char        := chr(9);    -- tab character
    q        char(3)        := '''"''';    -- quoted double quotes for csv
    cq        char(4)        := ''',"''';    -- comma and double quotes
    con1        char(8)        := '||'||cq||'||'; -- concat chrs w/comma+quote
    con2        char(5)        := '||'||q;    -- concat chrs w/quotes
    first_time    boolean        := true;    -- "first-time" flag
    procedure wrt (x in varchar2) is        -- Output procedure
        begin
            dh_file.file_prt(1,x);
        end;
begin
    dh_file.file_open(1,path,filename,'W');    -- Opens file for tailored code
-- *********************************************************************************
-- Assemble script to output table contents to .csv file
--     (The following PL/SQL code writes a PL/SQL block to "temp.sql", which will
--     contain tailored commands to produce Comma-Separated Values to a flat file
--     named after the table: "<Tablename>.csv". The last command of this script
--     executes "temp.sql", thus producing "<Tablename>.csv". Once this scripts
--     executes, you can review "temp.sql" to understand the CSV generation better.
-- *********************************************************************************
    wrt('declare');
    wrt(t||'procedure wrt (x in varchar2) is');
    wrt(t||t||'begin');
    wrt(t||t||t||'dh_file.file_prt(1,x);');
    wrt(t||t||'end;');
    wrt('begin');
    wrt(t||'dh_file.file_open(1,'''||path||''','''||upper('&tabname')||'.csv'',''W'');');
    wrt(t||'for r in'||t||'(select '||q);
    for r in       (select column_name        -- this loop concatenates names of
            from user_tab_columns        -- table columns, separated by
            where table_name = upper('&tabname') -- double quotes and commas
            order by column_id) loop
        if first_time then
            first_time := false;
            wrt('||'||r.column_name||con2);
        else
            wrt(con1||r.column_name||con2);
        end if;
    end loop;
    wrt(t||t||'x from &tabname) loop');
    wrt(t||t||'wrt(r.x);');
    wrt(t||'end loop;');
    wrt(t||'dh_file.file_close(1);');
    wrt('end;');
    wrt('/');
    dh_file.file_close(1);                -- closes "temp.sql"
end;
/
@temp
REM *********************************************************************************
REM End of "CSVMaker.sql" Script
REM *********************************************************************************

SQL> @CSVMaker
Provide the tablename for which you want a .csv file: s_region
SQL> get s_region.csv
  1  "1","North America"
  2  "2","South America"
  3  "3","Africa / Middle East"
  4  "4","Asia"
  5* "5","Europe"
SQL> @csvmaker
Provide the tablename for which you want a .csv file: s_dept
SQL> get s_dept.csv
  1  "10","Finance","1"
  2  "31","Sales","1"
  3  "32","Sales","2"
  4  "33","Sales","3"
  5  "34","Sales","4"
  6  "35","Sales","5"
  7  "41","Operations","1"
  8  "42","Operations","2"
  9  "43","Operations","3"
10  "44","Operations","4"
11  "45","Operations","5"
12  "50","Administration","1"
13* "51","Information Tech","1"
SQL>