Data can be exported to file from tables in Oracle database using SPOOL functionality.

Oracle Data Exporter

Data can be exported to file from tables in Oracle database using SPOOL functionality.

Employee Table: For exporting data into file. 
CREATE  TABLE employee(
   emp_no INTEGER,
   emp_name VARCHAR(50),
   job_title VARCHAR(30),
   manager_id INTEGER,
   hire_date Date,
   salary DECIMAL(18,2),
   commission DECIMAL(18,2),
   dept_no INTEGER
 )
  Primary key(emp_no);
Data: Populate the table using data present on this page


Sample Exporter Script 1: After login to oracle user 
SET FEEDBACK OFF
SET HEADING OFF
SET COLSEP '*|*'
SET TERMOUT OFF

spool /Users/user_name/tutorials/oracle_exporter.xls

SELECT * FROM employee;

spool off
SET TERMOUT ON

Note: Points to be noted for data export into file in oracle.
  1. 'HEADING off' is used to suppress column headers in the output file.
  2. 'COLSEP' is used to specify the delimiter between the columns in the output file.
  3. 'TERMOUT OFF' is used to suppress the display of the output so that output can be written in spool file without showing it on the screen.
  4. 'FEEDBACK OFF' is used to suppress the number of records returned by a SELECT statement.
  5. File name and path has to be specified using "spool filepath+filename" where data will be exported.
  6. Data return by query(SELECT * FROM employee) specified after the SPOOL command will be exported to the file. can also be added in the sqlldr scrip by using " infile 'filepath+filename'" after LOAD DATA command.
  7. 'SPOOL OFF' is used to indicate Oracle that export is complete and stop writing any data to exported file.


Sample Exporter Script 2: Running from UNIX/LINUX script file 
sqlplus -s << EOF >/dev/null
oracle_username/password@'"(description=(address=(host=hostname)(protocol=tcp)(port=portNo))(connect_data=(sid=sidname)))"'
SET FEEDBACK OFF
SET HEADING OFF
SET COLSEP '*|*'
SET TERMOUT OFF

spool /Users/user_name/tutorials/oracle_exporter.xls

SELECT * FROM employee;

spool off
SET TERMOUT ON
EXIT;
EOF;
Note: Points to be noted for data exporter using UNIX/LINUX script file.
  1. 'EOF' in the above script can be anything, it will specify Oracle to execute command between the two 'EOF' and then exit
  2. '/dev/null' is used to ignore the script output.
  3. Just run the UNIX/LINUX script in the same way as normal UNIX/LINUX script runs.

Download Sample Files