Loading data from Oracle into flat files

How do I load data from Oracle tables into flat files? Database version : 7.3.4.0.0 on digital Unix 4.0d, Oracle client on a Windows NT workstation.

    Requires Free Membership to View

    Login

There are a couple of tools on the market that do exactly as you describe. In fact, one can even use Microsoft's Data Transformation Services (DTS) to move data from an Oracle database into a flat file.

One of the easiest solutions is to use SQL*Plus. And it's free!! Just spool the output from a SQL statement to a file. You'll want to turn off headings and other things so that they won't appear in your flat file. For instance, to generate a flat file with all of the columns from the EMP table, you might perform a series of commands such as the following in SQL*Plus: 

set heading off 
set feedback off 
set pagesize 0 
spool emp.txt 
select * from emp; 
spool off 
set heading on 
set feedback on 
set pagesize 0
The EMP.TXT file will now contain the data. This produces a flat file which has fixed field lengths. Many times, people are asked to create a comma-delimited flat file. That can be accomplished similarly. But you'll have to add the commas between each column's values. This can be done as follows: 
set heading off 
set feedback off 
set pagesize 0 
spool emp.txt 
select empno||','||ename||','||job||','||mgr from emp; 
spool off 
set heading on 
set feedback on 
set pagesize 0

For More Information


This was first published in December 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top