When using SQLcl with Oracle Database, you can export your query results to a JSON file with the SPOOL command.

Example

Here’s an example that exports a whole table:

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

Here’s what it did, line by line:

• The first line sets SQLFORMAT to json. This ensures that our resulting .json file does in fact contain JSON. You can alternatively use json-formatted to output the JSON in a more human friendly format (more on this later).
• The second line uses the SPOOL command to specify where the output file will be written. Be sure to change /Users/barney/data/regions.json to a location on your system, and an appropriate file name.
• On the third 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.
• Lastly, I set SQLFORMAT back to my original setting, which was ansiconsole. This is optional – you could leave it at json if you prefer, or change it to something else.

Here’s what the resulting file looks like:

{"results":[{"columns":[{"name":"REGION_ID","type":"NUMBER"},{"name":"REGION_NAME","type":"VARCHAR2"}],"items":
[
{"region_id":1,"region_name":"Europe"}
,{"region_id":2,"region_name":"Americas"}
,{"region_id":3,"region_name":"Asia"}
,{"region_id":4,"region_name":"Middle East and Africa"}
]}]}
4 rows selected. 

As mentioned, you also have the option of using json-formatted to output the JSON in a more human-friendly format, with indents, etc. I’ve included an example of this at the bottom of this article.

Remove Feedback

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

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

Result:

{"results":[{"columns":[{"name":"REGION_ID","type":"NUMBER"},{"name":"REGION_NAME","type":"VARCHAR2"}],"items":
[
{"region_id":1,"region_name":"Europe"}
,{"region_id":2,"region_name":"Americas"}
,{"region_id":3,"region_name":"Asia"}
,{"region_id":4,"region_name":"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 json;
SET FEEDBACK off;
SPOOL '/Users/barney/data/regions_feedback_off.json' APPEND;
SELECT * FROM regions;
SPOOL off;
SET FEEDBACK on;
SET SQLFORMAT ansiconsole;

Resulting file:

{"results":[{"columns":[{"name":"REGION_ID","type":"NUMBER"},{"name":"REGION_NAME","type":"VARCHAR2"}],"items":
[
{"region_id":1,"region_name":"Europe"}
,{"region_id":2,"region_name":"Americas"}
,{"region_id":3,"region_name":"Asia"}
,{"region_id":4,"region_name":"Middle East and Africa"}
]}]}
{"results":[{"columns":[{"name":"REGION_ID","type":"NUMBER"},{"name":"REGION_NAME","type":"VARCHAR2"}],"items":
[
{"region_id":1,"region_name":"Europe"}
,{"region_id":2,"region_name":"Americas"}
,{"region_id":3,"region_name":"Asia"}
,{"region_id":4,"region_name":"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 query output 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 json;
SET FEEDBACK off;
SPOOL '/Users/barney/data/regions_feedback_off.json' REPLACE;
SELECT * FROM regions;
SPOOL off;
SET FEEDBACK on;
SET SQLFORMAT ansiconsole;

Resulting file:

{"results":[{"columns":[{"name":"REGION_ID","type":"NUMBER"},{"name":"REGION_NAME","type":"VARCHAR2"}],"items":
[
{"region_id":1,"region_name":"Europe"}
,{"region_id":2,"region_name":"Americas"}
,{"region_id":3,"region_name":"Asia"}
,{"region_id":4,"region_name":"Middle East and Africa"}
]}]}

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

Formatted JSON

You can alternatively use json-formatted to output the JSON in a more human friendly format:

SET SQLFORMAT json-formatted;
SPOOL '/Users/barney/data/regions_formatted.json';
SELECT * FROM regions;
SPOOL off;
SET SQLFORMAT ansiconsole;

Result:

{
  "results" : [
    {
      "columns" : [
        {
          "name" : "REGION_ID",
          "type" : "NUMBER"
        },
        {
          "name" : "REGION_NAME",
          "type" : "VARCHAR2"
        }
      ],
      "items" : [
        {
          "region_id" : 1,
          "region_name" : "Europe"
        },
        {
          "region_id" : 2,
          "region_name" : "Americas"
        },
        {
          "region_id" : 3,
          "region_name" : "Asia"
        },
        {
          "region_id" : 4,
          "region_name" : "Middle East and Africa"
        }
      ]
    }
  ]
}