After 20 some years in the database world, I know at least two things:

1. the database is almost always the best place to store your data
2. developers and analysts want Excel files, CSV, or JSON

I don't want to argue the first point. There is plenty to talk about in terms of relational (SQL databases) vs NoSQL (not only SQL databases). And maybe we can talk about that later.

What I want to talk about today is the second point - how to get that data in the format you want to run your javascript/python/Excel macros against.

Because, that's your universe. SQL isn't your native tongue. I get that, and I'm not going to fight it. So let me help you.

Oracle's free tools make this easy

While I'm going to be using our Oracle Database as the source of our database in these examples, you should know that Oracle SQL Developer can also connect to PostgreSQL, SQL Server, DB2, Sybase, Teradata, MySQL, Redshift...and oh yeah, Azure SQL.

Desktop GUIs..point and click!

Oracle SQL Developer is free. It's available on any desktop that supports Oracle Java, and that Oracle Java is free to use with SQL Developer.

Once you're connected, you're going to either be browsing tables or views, or running queries.

I'm looking at data, give it to me in...CSV!

You're going to want to pick up your mouse at this point Right-click in the Grid of rows and columns that is your query results or object contents in the Data panel.

Pick your poison.

Your options here are quite flexible.

CSV, delimited, Excel (xlsx), Excel (xls), fixed width, HTML, INSERT statements, JSON, formatted (pretty) JSON, SQL*Loader, T2, text, and our really old friend - XML.

So for example if I ask for the pretty JSON option of looking at my top rated beers from Raleigh, NC, I'd get something like this -

A pretty-formatted JSON array collection of my personal Untappd data

Two Tricks

1. You can put the export anywhere, not to just a files.

I really like exporting directly to my Clipboard or to my SQL Developer Editor (the Worksheet option)

1. You can configure your favorites as the default.

I almost always want pretty JSON to my clipboard, so just default to that.

Save yourself some clicks! If you're always removing the string quotes, just go into preferences and set the default enclosures on CSV to null.

Desktop GUIs..just let me type what I want!

The mouse is a necessary evil, to be avoided as much as possible. Believe me, I get it. So we give you another workflow.

Just type out what you want.

select /*json-formatted*/ *
  from untappd
 where rating_score > 3.75
   and brewery_city = 'Raleigh'
 order by beer_name;

The client sees your comment, and knows what you want.

There are two components to using this feature.

1. Adding the comment, the comment matches the label in the GUI.
2. Executing the command with F5, or the 2nd button (Execute as Script)

The options are case-sensitive, csv will work but CSV won't.

You have one other option...let's say you want INSERT statements BY DEFAULT.

Simply use this command -

set sqlformat insert

Every query will return INSERT statements..until you set sqlformat to null or something else.

GUIs suck, boomer. Gimmer a proper bash terminal!

Ok, us 'boomers' grew up on command-line interfaces (CLI), so go easy on us. I'm not going to start a religious war on GUI vs CLI, but let's just assume you really like CLI.

P.S. I know it's not bash anymore. But you know what I mean when I say 'bash.'

We have another free to use utility for you, it's called SQLcl(short for SQL command-line.)

It's super easy to install, just download and unzip. No license click through or (Oracle) accounts required. It's also in our yum repo.

Remember what I showed above? It's basically the same here. But there's no mouse and clicking, so all the comment and set sqlformat stuff, you'll be using that here.

I'm not blind, just trying to make the text easier for you to make out :)

Remember how we did the set sqlformat, but then we also had some defaults we could set for the wizards in the GUI before?

Well, we have some defaults we can set here as well.

set sqlformat delimited |^| ' '

Here I've shown how you can get a delimited text file, you pick the 'delimiter' and you choose how to quote your strings.

But Jeff, I want that in a FILE.

Easy-peasy. In the Oracle CLI world, we have a command called SPOOL.

cd /path/to/my/file
spool myfile.csv

The cd command allows me to tell SQLcl where to read/write files, SPOOL says copy output to said file.

What about that fancy web stuff you showed us in your first post?

So yeah, if you're in our Oracle Cloud and Autonomous Database service, we give you a Web based SQL client. It's also available on-premises as well (video tutorial), if you want to setup and run our REST API technology.

You can do most of what I've shown you already here as well.

Quickly download a CSV, JSON, XML, or TXT file of your query results.

But I want to try the HTML!

Simply add the html comment in your query, and execute as a script, you'll get the HTML output back to your script panel.

Our HTML report is basic, but does include a quick filtering mechanism with some JavaScript.

That's pretty much it, but just one more thing.

Proof that my kids aren't wrong when they call me boomer.

1. That's Columbo, portrayed by the great, late Peter Falk.
2. 'Ok, boomer' doesn't mean you're 80. It means, your experiences aren't ours. That's my kids' definition at least.

I have CSV, make it into a table!

Yeah, we can take your CSV, JSON, XML, Excel files and import that to existing or new tables as well. I'm not going to walk through that now, because I kinda already have (The Movie!)