×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Oracle: All versions FAQ

Tips and Tricks

How can I generate flat files of Comma-Separated Values (CSV) from Oracle tables? by SantaMufasa
Posted: 26 Feb 04

Here are routines that create Comma-Separated Value (.csv) files from a table you specify (except tables with LONG columns). (Disclaimer: I have not stress tested the routines to find the upper limits on total number of columns or total combined column widths.)

The first code set is a generic package that facilitates use of Oracle's "utl_file" flat-file-manipulation package. My actual CSV-generating script relies upon the "dh_file" package, so make sure you save the "dh_file" package to a script and run it from the schema from which you wish to "CSV" tables. Also, if you have not done so already, ensure that your instance's parameter, "utl_file_dir" equals either "*" (able to write to any path on the server's file system) or "<some selected path(s)>" to which you will write output from the second code set.

Code Set 1: DH_FILE "utl_file" package handler.

CODE


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 **************************************************************

Code Set 2: Script to generate Comma Separated Values from a prompted table:

CODE


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 *********************************************************************************

Here are some sample invocations and brief results illustrating proof of concept for "CSVMaker.sql":

CODE


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>

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:44 (26Feb04) UTC (aka "GMT" and "Zulu"), 11:44 (26Feb04) Mountain Time)

Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close