How do I export a database table to a flat file ?

The Oracle (tm) Users' Co-Operative FAQ

How do I export a database table to a flat file.

The simplest method of exporting Oracle data to a flat file is to use the spool file feature of SQL*Plus, which will create a fixed format output file. To create a clean file several SQL*Plus settings need to be modified to remove unwanted SQL*Plus messages from the output. Using the emp table common to the Oracle manuals here is a short sample sql script to create a fixed format spool file: [Comments added after run]

--
set echo off        -- suppress showing sql in result set
set feedback off    -- eliminate row count message
set linesize 100    -- make line long enough to hold data
set pagesize 0      -- suppress headings and page breaks
set sqlprompt ''    -- eliminate SQL*Plus prompt from output
--                     other useful parameters
set trimspool on    -- eliminate trailing blanks
spool emp
select *
from   emp
where  rownum < 6;
spool off
复制

And here is the result set:

      7369 SMITH      CLERK           7902 17-DEC-80        800              20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300   30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500   30
      7566 JONES      MANAGER         7839 02-APR-81       2975              20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400   30
复制

By resorting to using a column list and the concatenation symbol, ||, a comma delimited file can easily be created.

select empno||','||ename||','||job||','||hiredate
from   emp
where  rownum < 6;
7369,SMITH,CLERK,17-DEC-80
7499,ALLEN,SALESMAN,20-FEB-81
7521,WARD,SALESMAN,22-FEB-81
7566,JONES,MANAGER,02-APR-81
7654,MARTIN,SALESMAN,28-SEP-81
复制

SQL to generate the select list for you is fairly easy to write:

set echo off
rem
rem SQL*Plus script to create comma delimited output file from table
rem
rem 20000614  Mark D Powell   Automate commonly done task
rem
set pagesize 0
set verify   off
set feedback off
set linesize 130
accept owner    prompt 'Enter table owner => '
accept tblname  prompt 'Enter table name => '
spool csv2.sql
select 'select ' from sys.dual;
select decode(column_id,1,column_name,
             '||'',''||'||column_name)
from   sys.dba_tab_columns
where  table_name = upper('&&tblname')
and    owner      = upper('&&owner')
order by column_id;
select 'from &&owner..&&tblname;'
from   sys.dual;
spool off
undefine owner
undefine tblname
复制

This code produces screen output like:

UT1> @csv
Enter table owner => mpowel01
Enter table name => emp
select
EMPNO
||','||ENAME
||','||JOB
||','||MGR
||','||HIREDATE
||','||SAL
||','||COMM
||','||DEPTNO
from mpowel01.emp;
复制

This output is stored in the file csv2.sql which in turn produces a file with contents like:

UT1> @csv2
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
复制

By substituting the result of the chr() function for the comma you can change the delimiter, perhaps to a tab, chr(9).

WARNING, you can not use this technique against columns of type long, raw, long raw, and LOB datatype as Oracle will not concatenate these.

Further reading: For more details on the SQL*Plus options for the set command see the Oracle SQL*Plus manual or Jonathan Gennick's book Oracle SQL*Plus the Definitive Guide published by O'Reilly.