How to Prettify JSON Formatted Query Results in SQLcl (Oracle)

If you use SQLcl to query Oracle Database, you might be aware of the SET SQLFORMAT json option, that makes query results come back as JSON documents.

But there’s also a json-formatted option, which returns the results in a more human readable format.

Example

Here’s an example to demonstrate:

SET SQLFORMAT json-formatted;
SELECT * FROM regions;

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"
        }
      ]
    }
  ]
}

As a comparison, here it is using the json setting (i.e. without the result being prettified):

SET SQLFORMAT json;
SELECT * FROM regions;

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"}
]}]}