Use Spool in Oracle to Export Query Results to CSV

In this short post, I’ll show you how to use Spool in Oracle to export query results to CSV.

I would also show you how to apply a quick tweak in order to export the query results to a text file.

Use Spool in Oracle to Export Query Results to CSV

To start, here is the generic structure that you may run in Oracle to export query results to CSV:

 

spool 'Path where you'd like to store the exported file\your_file_name.csv';
SELECT * FROM database.table WHERE condition;
spool off;

 

And this is how the generic syntax would look like in Oracle SQL Developer:

 

Use Spool in Oracle to Export Query Results to CSV

 

Note that in order to execute the Spool in Oracle, you’ll need to run it as a script (for example, if you are using Oracle SQL Developer, you may press F5 to run the Spool as script).

Your CSV file will then get created at your specified path.

But what if you want to use Spool in Oracle to export query results to a Text file?

In the next section, you’ll see how to accomplish this task.

Export Query Results to a Text File

In order to export the query results to a text file you only need to modify the file type from csv to txt at the end of your path.

And just in case you wonder, here is the generic structure that you may apply in Oracle to export query results to a text file:

 

spool 'Path where you'd like to store the exported file\your_file_name.txt';
SELECT * FROM database.table WHERE condition;
spool off;

 

And as before, don’t forget to press F5 in order to run the Spool and then generate the file at your specified path.

Conclusion

We just saw how to use Spool in Oracle to export query results to CSV and Text files.

Now if you’re dealing with SQL Server, you may then want to check the following source that explains how to export query results to a text file.

You may read more about Spool by visiting the Spool – Oracle Docs.