Problem: I have 3 queries I want to run. I want the end result to be a single spreadsheet that has all the query results included.

This problem may sound familiar to you, I have talked about how to do this with the GUI here – but you get one workbook per exported TABLE.

The problem with my previous ‘solution’ is that you would need to code your queries to database VIEWs and then export those.

Here’s a quick and dirty way to get everything you want to an ‘excel’ file. You’ll get a CSV file, which you can then open in Excel and convert if you’d like.

My queries are simple, number of rows are small – to make the post simple, but you can substitute your stuff and should be A-OK.

cd c:\users\jdsmith
SET sqlformat csv
SET feedback off
SET echo off
spool blog_queries_excel.csv
SELECT * FROM regions;
SELECT * FROM locations;
SELECT * FROM departments;
spool off

Execute this code in SQL Developer with F5 or in SQLcl..and your output will look like so, when opened in Excel:

Ta-da.

What does the code do?

cd tells us where to read and write for working with files (it changes the SQLPath essentially)

set sqlformat csv tells us to take the ouptut and put it through our formatter, using the csv style.

set feedback off tells us to suppress messages like ’27 rows selected’ or ‘table created’

set echo off tells us not to include the query that was executed in the output

spool tells us to copy the output to a file

If you want to suppress the output in the console or script output panel and JUST write to the file, then do this

But Jeff, I want lines between my tables…

Then change the script…turn feedback back on, or use PROMPTs or simply select the whitespace as desired into the output.

I hope this helps!

End of day, when you’re done with your file, and you’re in Excel, you’ll start cleaning it up IN EXCEL. All of this I’ve just shown you is just a kickstarter to get the data into the file that much faster.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

6 Comments

  1. Outstanding knowledge you have here! – I’m very grateful I found your blog and by reading and trying, I get some knowledege to understand better how use SQL Developer and PL/SQL in my current job position (and why not, maybe in other job positions too).
    Thank you for share your knowledge.

  2. Matthew Holloway Reply

    Storing this away in my rainy day file. I won’t need this often but this is looks way less painful than what I was doing.

  3. Have I told you lately how much I appreciate you and all you do?

    Thanks, Jeff for being wonderful!

    ~Melody

  4. Hi Jeff,
    Great post and here is an unrelated question I haven’t found an answer to.
    Is there a way to share a report and make it, the SQL, not editable?
    I trust the end users, the DBAs do not.
    I thought possibly keeping the file in a shared folder and restricting permissions on the folder.
    The Reports have Bind variables so each viewer could run it for their data without changing the underlying SQL.
    Is this possible? The higher level question not necessarily my thought of how to do it.

    • thatjeffsmith

      Sure, create your report in the database as a view or stored procedure…or an APEX Application.

Write A Comment