Re: Oracle Server 7.3 table to .CSV file dump
Date: 1998/04/29
Message-ID: <01bd73cb$8f8bc8a0$a504fa80_at_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 CEST