How do I load data from Oracle tables into flat files? Database version : 22.214.171.124.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 0The 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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.