Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ASCII export of table contents
In article <32E0DDCA.41DF_at_internet.sk>, delta_at_internet.sk says...
>
>Does anybody know how to export table contents in readable ASCII
>format (with delimiters or in fixed width format). Import is
>possible with sqlload but what about export ?
Assuming you're using SQL*Plus:
column c1 format A11
column c2 format A6
column c3 format A10
REM Recommend formatting all output columns as char REM Less chance of problems
SELECT TO_CHAR(some_date,'DD-MON-YYYY') c1,
TO_CHAR(some_number,'999.9') c2, some_char_column c3 FROM a_table
SPOOL ascii_file.txt
/
SPOOL OFF;
2) For comma-delimited:
SELECT TO_CHAR(some_date,'DD-MON-YYYY')||','||
TO_CHAR(some_number,'999.9')||','|| some_char_column
SPOOL ascii_file.txt
/
SPOOL OFF;
In both cases use the following SET statements:
SET pagesize 0; /* turn off page breaks */
SET verify off; /* if applicable */
SET heading off; /* turn off column headings */
ttitle off;
btitle off;
Also set your linesize large enough to accommodate the output line.
-- _________________________________________ | Steve Jakob | Information Systems | London Regional Cancer Centre | sjakob_at_lrcc.on.ca |_______________________________________|Received on Tue Jan 21 1997 - 00:00:00 CST