Re: Oracle Server 7.3 table to .CSV file dump

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: 1998/04/29
Message-ID: <01bd73cb$8f8bc8a0$a504fa80@mndnet>#1/1

Try this script. it is written by someone else and I used it and works great.

• dump.sql - jared still
• jkstill_at_bcbso.com
• jkstill_at_teleport.com --
• dump a table to a comma delimited ascii file
• only drawback is line length is likely to be padded with
• quite a few spaces if the 'set trimspool on' option is
• not in your version of SQLPLUS --
• also builds a control file and a parameter file for SQL*LOADER

set trimspool on
set serverout on
clear buffer
undef dumpfile
undef dumptable
undef dumpowner
var maxcol number
var linelen number
var dumpfile char(40)
col column_id noprint
set pages0 feed off termout on echo off verify off accept dumpowner char prompt 'Owner of table to dump: ' accept dumptable char prompt 'Table to dump: ' begin

        select max(column_id) into :maxcol
        from all_tab_columns
        where table_name = rtrim(upper('&dumptable'))
        and owner = rtrim(upper('&dumpowner'));
        select sum(data_length) + ( :maxcol * 3 ) into :linelen
        from all_tab_columns
        where table_name = rtrim(upper('&dumptable'))
        and owner = rtrim(upper('&dumpowner'));

end;
/

Tony Howard <tonyh_at_gmap.leeds.ac.uk> wrote in article <3546F1BC.E143C216_at_gmap.leeds.ac.uk>...
> I need to dump oracle tables to proper comma delimited format files with
> strings in quotes, i.e. the same format you can use in sqlldr & most
> wordprocessors.
> Any clues much appreciated.
>
>
Received on Wed Apr 29 1998 - 00:00:00 CDT