Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

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


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close