Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Exporting to a tab delimited file - no spaces
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)
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-lReceived on Mon Oct 04 2004 - 17:24:13 CDT
![]() |
![]() |