Unloading Data

Oracle provides Sql*Loader utility and  External table object to load data into a database from a flat text, cvs or any file type. Have your ever though how those flat files are generated? It could be some one generating manually or from another database management system. There are few DBMS that has unload utility. Oracle does not provide unload tool or utility but there are ways you can unload data to a flat file even the tool does not exist. With this article, we will demonstrate how to unload data to a file from a table to send it to a requester or to load it to another database.

As always, we are going to use world's famous hr.employee table for our experiment. We will unload data from hr.employee to a text file using multiple ways. There are no tools provided from Oracle but there are third party software that are available and may charge for a license fee therefore, we are going to use Sql*Plus SPOOL feature to unload it. I am excited to unload, are you ready to explore the power of SPOOL for absolutely free of cost, no licensing fee required.

Solution 1: 

set pagesize 0
set feedback off
set heading off
spool /home/dbarepublic/spool/employee_data.txt
SELECT empno
    ||','
    ||ename
    ||','
    ||job
    ||','
    ||mgr
    ||','
    ||hiredate
    ||','
    ||sal
    ||','
    ||comm
    ||','
    ||deptno
FROM hr.employee
WHERE deptno = 25
spool off

Spool Output: employee_data.txt
100,joe,dba,dave,10-JAN-1999,230000,null,25 
102,jackie,dba,dave,25-JAN-2005,130000,null,25 
103,john,dba,dave,10-JAN-2009,130000,null,25 
104,dino,dba,dave,10-JAN-2016,130000,null,25
...
..
.

Solution 2:

set pagesize 0
set linesize 150
set echo off
set feedback off
set verify off
set heading off
set termout off
set trimout on
set trimspool on
set colsep |
spool /home/appbatch/prabin/solo/test_spool.txt
SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    comm,
    deptno
FROM hr.employee
WHERE deptno = 25
spool off

Spool Output: employee_data.txt
100,joe,dba,dave,10-JAN-1999,230000,null,25 
102,jackie,dba,dave,25-JAN-2005,130000,null,25 
103,john,dba,dave,10-JAN-2009,130000,null,25 
104,dino,dba,dave,10-JAN-2016,130000,null,25
...
..
.
What difference did you notice between these solutions? They both are pretty much the same except one's query looks messier than other. The queries to above solutions are pretty straight forward but set commands. Those set commands pretty much control how you want the output to the file. Below, I am going to explain how these set  commands works so you can control output file.

ECHO {ON|OFF}: Display  SQL command as they are executed
PAGESIZE {n} The height of the pages  -number of lines, 0 will suppress all headings,
 page breaks and title.
FEEDBACK {ON|OFF}: Display the number of records returned.
HEADING {ON|OFF}: Print column heading
LINESIZE {n}: Width of a line before wrapping to the next line and the default is 150.
TERMOUT {ON|OFF}: Suppress/display the output from a command to a terminal.
TRIMOUT {ON|OFF}: Display or remove blanks at the end of each line. Ignores unless set TAB is ON.
TRIMSPOOL {ON|OFF}: Suppress/allows trailing blank at the end of each spooled line.
COLSEP {<|,}: The text to be printed between selected columns.

The first solution is what I have been using for years before I came to know about set colsep commands. I now prefer solution 2 and have started using it since the query is very easy to read unlike solution 1.

Beside third-party tools what other options do you know? UTIL_FILE is a Oracle supplied  package that lets you read and write to a file which may another options to unload data to a file. To use UTIL_FILE package a PL/SQL knowledge is recommended.