How to Export data to a flat file

Whenever you want to move data from oracle to other software products like SQL SERVER or MYSQL or any other database software then it is needed at first to move data to a flat file. Flat file is an OS file like a text file. Also, if you don't have oracle net then for moving data from lower verion to upper version you can also use this method - first save data to a flat file and then using SQL*Loader or external table transfer data into database.

To illustrate the system I have create table test_spool and insert data into it.


A)SQL> create table test_spool( a number, b varchar2(10),c varchar2(30));
Table created.

SQL> insert into test_spool values(1,'Oracle','Bangladesh , India and USA');
1 row created.

SQL> select * from test_spool;
A B C
---------- ---------- ------------------------------
1 Oracle Bangladesh , India and USA

Now Set the following environmental variables of SQL*Plus.
B)SQL> SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET COLSEP " "
I used COLSEP in order to separate column inside flat file.
Now spool it and run the query.
C)SQL> spool output_to_flat_file.txt
SQL> select * from test_spool;
1 Oracle Bangladesh , India and USA
SQL> spool off
See the contents of the file now.

D)SQL> !cat output_to_flat_file.txt
SQL> select * from test_spool;
1 Oracle Bangladesh , India and USA
SQL> spool off

Use it as you like.

In order to save the query to an html file you can use as follows.


SET HEADING ON
SET MARKUP HTML ON SPOOL OFF
SPOOL /oradata2/a.html
SELECT * FROM LOGIN WHERE ROWNUM<5;
SPOOL OFF

To save it in excel file paste this output to an excel file.