Re: Exporting to a tab delimited file - no spaces

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Tue, 05 Oct 2004 00:27:08 +0200
Message-ID: <4161CE3C.4060200@roughsea.com>

Kristen Cameron wrote:

>List,
>
>One of my clients wants a script that will export 28 tables to a tab-delimited format. I have managed to use 'set colsep' to set the delimiter to a tab. This resulted in a tab-delimited file, but all the fields were padded with spaces to the max field width. The spaces cause problems for the software destined to use these files.
>
>The other options is select col1 || chr(9) || col2 ... etc., but I don't want to have to type each column name for 28 tables. I also know lots of ways to clean up the files once they are created, but I'm looking for a way to get a clean file right from the SQL prompt.
>
>Does anyone have a neat trick to help out with this one?
>Oracle 8.1.7 running on Windows 2000.
>
>Thanks!
>
>------------------------------------------------------
>Kristen Cameron
>
>Database Administrator | Administrateur de bases données
>Information Management Group | Groupe de gestion de l'information
>Indian and Northern Affairs Canada | Affaires Indiennes et du Nord Canada
>Yellowknife, NT X1A 2R3
>(867)669-2536 | cameronkgd_at_inac.gc.ca
>-----------------------------------------------------
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>
>

Kristen,

    What you want is a double-action query; becomes tedious when you have too many quotes, but this isn't your case. Something like this should do :

set pagesize 0
set recsep off
set trimspool on
set feedback off
set termout off
spool spit_data.sql

select decode(COLUMN_ID, 1, 'spool ' || TABLE_NAME || '.dat' || chr(10) ||
                            'select ',
                            ' || chr(9) || ') ||
       decode(T, 'D', 'to_char(' || COLUMN_NAME || ', ''MM/DD/YYYY 
HH24:MI:SS'')',
                 'N', 'to_char(' || COLUMN_NAME || ')',
                      COLUMN_NAME) ||
       decode(COLUMN_ID,
                  LAST_ID,  chr(10) || 'from ' || TABLE_NAME || ';' || 
chr(10) ||
                           'spool off',
                           '')
from (select TABLE_NAME,
             COLUMN_ID,
             max(COLUMN_ID) over (partition by TABLE_NAME) LAST_ID,
             decode(DATA_TYPE, 'DATE', 'D',
                               'NUMBER', 'N',
                               'FLOAT', 'N',
                                        'S') t,
             COLUMN_NAME
      from USER_TAB_COLUMNS
      where TABLE_NAME in (list of 28 tables)
      order by TABLE_NAME, COLUMN_ID)

/
spool off
set linesize 4000 -- or whatever is required @spit_data

assuming of course you have no nasty datatype, eg LOBs or LONGs.

It will generate one .dat file per table.

-- 
Regards,

Stephane Faroult

RoughSea Ltd
http://www.roughsea.com




--
http://www.freelists.org/webpage/oracle-l

Received on Mon Oct 04 2004 - 17:24:13 CDT