How to Export Query Results to a .txt File when using SQLcl (Oracle)

by

When querying Oracle Database, you can use the SPOOL command to export your query results to a text file when using SQLcl.

Example

Here’s an example that exports a whole table:

SPOOL '/Users/barney/data/regions.txt';
SELECT * FROM regions;
SPOOL off;

Here’s what it did, line by line:

  • The first line uses the SPOOL command to specify where the output file will be written. Be sure to change /Users/barney/data/regions.txt to a location on your system, and an appropriate file name.
  • On the second line, I ran the SQL query – the results for which I’m exporting. In this case, I exported the whole regions table.
  • Next, I turned SPOOL off.

Here’s what the resulting file looks like:

   REGION_ID               REGION_NAME 
____________ _________________________ 
           1 Europe                    
           2 Americas                  
           3 Asia                      
           4 Middle East and Africa    

4 rows selected.

In my case, my SQLFORMAT was set to ansiconsole, and so the output reflects that.

You can change this to any format that suits.

Here’s an example of exporting the result in a format that can be used as an import file with sql*loader:

SET SQLFORMAT loader
SPOOL '/Users/barney/data/regions_loader.txt';
SELECT * FROM regions;
SPOOL off;
SET SQLFORMAT ansiconsole

Result:

 1|"Europe"|
 2|"Americas"|
 3|"Asia"|
 4|"Middle East and Africa"|

4 rows selected.

In this case I also reset the SQLFORMAT back to ansiconsole once the SPOOL operation had completed.

Remove Feedback

You can remove the X rows selected with SET FEEDBACK off:

SET SQLFORMAT ansiconsole
SET FEEDBACK off
SPOOL '/Users/barney/data/regions_no_feedback.txt';
SELECT * FROM regions;
SPOOL off;
SET FEEDBACK on

Result:

   REGION_ID               REGION_NAME 
____________ _________________________ 
           1 Europe                    
           2 Americas                  
           3 Asia                      
           4 Middle East and Africa

In this case I turned FEEDBACK back on after exporting the file.

Append the Results

By default, SPOOL uses REPLACE, which replaces the file if it already exists.

However, we can use the APPEND argument to append the results to the file.

Example:

SET SQLFORMAT ansiconsole
SET FEEDBACK off
SPOOL '/Users/barney/data/regions_no_feedback.txt' APPEND;
SELECT * FROM regions;
SPOOL off;
SET FEEDBACK on

Resulting file:

   REGION_ID               REGION_NAME 
____________ _________________________ 
           1 Europe                    
           2 Americas                  
           3 Asia                      
           4 Middle East and Africa    

   REGION_ID               REGION_NAME 
____________ _________________________ 
           1 Europe                    
           2 Americas                  
           3 Asia                      
           4 Middle East and Africa

This example appended the results to the file that was created (and populated) in the previous example. This resulted in the result being duplicated in the file.

Overwrite the Existing File

We can use REPLACE to overwrite the existing file with the results of a new query:

SET SQLFORMAT ansiconsole
SET FEEDBACK off
SPOOL '/Users/barney/data/regions_no_feedback.txt' REPLACE;
SELECT * FROM regions;
SPOOL off;
SET FEEDBACK on

Resulting file:

   REGION_ID               REGION_NAME 
____________ _________________________ 
           1 Europe                    
           2 Americas                  
           3 Asia                      
           4 Middle East and Africa

As mentioned, REPLACE is the default setting, so we could have simply omitted this argument altogether.