SQLcl Formatting Options (Oracle)

When using SQLcl to query Oracle Database, you can use the SET SQLFORMAT command to determine the format of the results.

You can also use inline comments to specify the format from directly within your query.

For example, you can use the following to output the results in CSV format:

SET SQLFORMAT csv;
SELECT * FROM regions;

Or you can do it like this:

SELECT /*csv*/ * FROM regions;

Formatting Options

The following options can be used to specify the output format:

Option	Description
`default`	Clears all formatting and sets it to SQL*PLUS style formatting. This option only works with the `SET SQLFORMAT` command.
`ansiconsole`	Advanced formatting based on data and terminal size.
`fixed`	Fixed width.
`csv`	Comma separated format with strings enclosed in double quotes (`"`).
`loader`	Pipe (`\|`) delimited format with strings enclosed in double quotes (`"`).
`delimited`	CSV format with optional separator, left, and right enclosure.
`text`	Outputs the results as text, without separators. This option isn’t documented in the `HELP` option.
`insert`	Generates SQL `INSERT` statements from the results.
`json`	JSON format matching ORDS Collection Format.
`json-formatted`	JSON format matching ORDS Collection Format and pretty printed. The JSON is presented in a more human readable format.
`xml`	XML format.
`html`	HTML tabular format. Generates the code for an HTML document with a table with styling and a JavaScript search tool.

Examples

Below are examples to demonstrate the above options.

`default`

Clears all formatting and sets it to SQL*PLUS style formatting.

SET SQLFORMAT default;
SELECT * FROM regions;

Result:

SQL Format Cleared

 REGION_ID REGION_NAME              
---------- -------------------------
         1 Europe                   
         2 Americas                 
         3 Asia                     
         4 Middle East and Africa

`ansiconsole`

Advanced formatting based on data and terminal size.

SET SQLFORMAT ansiconsole;
SELECT * FROM regions;

Result:

   REGION_ID               REGION_NAME 
____________ _________________________ 
           1 Europe                    
           2 Americas                  
           3 Asia                      
           4 Middle East and Africa

`fixed`

Fixed width.

SET SQLFORMAT fixed;
SELECT * FROM regions;

Result:

"REGION_ID"                   "REGION_NAME"                 
"1"                           "Europe"                      
"2"                           "Americas"                    
"3"                           "Asia"                        
"4"                           "Middle East and Africa"

`csv`

Comma separated format with strings enclosed in double quotes (").

SET SQLFORMAT csv;
SELECT * FROM regions;

Result:

"REGION_ID","REGION_NAME"
1,"Europe"
2,"Americas"
3,"Asia"
4,"Middle East and Africa"

`loader`

Pipe (|) delimited format with strings enclosed in double quotes (").

SET SQLFORMAT loader;
SELECT * FROM regions;

Result:

 1|"Europe"|
 2|"Americas"|
 3|"Asia"|
 4|"Middle East and Africa"|

`delimited`

CSV format with optional separator, left, and right enclosure. This enables you to choose your own delimiters.

SET SQLFORMAT delimited , < >;
SELECT * FROM regions;

Result:

<REGION_ID>,<REGION_NAME>
1,<Europe>
2,<Americas>
3,<Asia>
4,<Middle East and Africa>

`text`

Outputs the results as text, without separators. This option isn’t documented in the HELP option.

SET SQLFORMAT text;
SELECT * FROM regions;

Result:

"REGION_ID"null"REGION_NAME"
1null"Europe"
2null"Americas"
3null"Asia"
4null"Middle East and Africa"

`insert`

Generates SQL INSERT statements from the results.

SET SQLFORMAT insert;
SELECT * FROM regions;

Result:

REM INSERTING into REGIONS
SET DEFINE OFF;
Insert into REGIONS (REGION_ID,REGION_NAME) values (1,'Europe');
Insert into REGIONS (REGION_ID,REGION_NAME) values (2,'Americas');
Insert into REGIONS (REGION_ID,REGION_NAME) values (3,'Asia');
Insert into REGIONS (REGION_ID,REGION_NAME) values (4,'Middle East and Africa');

`json`

JSON format matching ORDS Collection Format.

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

`json-formatted`

JSON format matching ORDS Collection Format and pretty printed. The JSON is presented in a more human readable format.

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

`xml`

XML format.

SET SQLFORMAT xml;
SELECT * FROM regions;

Result:

<?xml version='1.0'  encoding='UTF-8' ?>
<RESULTS>
	<ROW>
		<COLUMN NAME="REGION_ID"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="REGION_NAME"><![CDATA[Europe]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REGION_ID"><![CDATA[2]]></COLUMN>
		<COLUMN NAME="REGION_NAME"><![CDATA[Americas]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REGION_ID"><![CDATA[3]]></COLUMN>
		<COLUMN NAME="REGION_NAME"><![CDATA[Asia]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REGION_ID"><![CDATA[4]]></COLUMN>
		<COLUMN NAME="REGION_NAME"><![CDATA[Middle East and Africa]]></COLUMN>
	</ROW>
</RESULTS>

`html`

HTML tabular format. Generates the code for an HTML document with a table with styling and a JavaScript search tool.

SET SQLFORMAT html;
SELECT * FROM regions;

The resulting HTML code is quite large, as it creates an HTML document, adds styles, JavaScript, etc.

Here’s what the resulting HTML looks like when saved to a .html file and rendered in a browser:

regions.html

And here’s the actual HTML code that was generated:

<!DOCTYPE html>
<html>

<head>
  <meta charset='UTF-8'>
  
  <title>Result Data</title>
  
  <meta name="viewport" content="width=device-width, initial-scale=1.0">

  
  <style>
  * { 
    margin: 0; 
    padding: 0; 
  }
  body { 
    font: 14px/1.4 Palatino, Serif; 
  }
  
  /* 
  Generic Styling, for Desktops/Laptops 
  */
  table { 
    width: 100%; 
    border-collapse: collapse; 
  }
  /* Zebra striping */
  tr:nth-of-type(odd) { 
    background: #eee; 
  }
  th { 
    background: #333; 
    color: white; 
    font-weight: bold; 
  }
  td, th { 
    padding: 6px; 
    border: 1px solid #9B9B9B; 
    text-align: left; 
  }
  @media 
  only screen and (max-width: 760px),
  (min-device-width: 768px) and (max-device-width: 1024px)  {
    table, thead, tbody, th, td, tr { display: block; }
    thead tr { position: absolute;top: -9999px;left: -9999px;}
    tr { border: 1px solid #9B9B9B; }
    td { border: none;border-bottom: 1px solid #9B9B9B; position: relative;padding-left: 50%; }
    
    td:before { position: absolute;top: 6px;left: 6px;width: 45%; padding-right: 10px; white-space: nowrap;}
    
    /*
    Label the data
    */
td:nth-of-type(1):before { content: "REGION_ID"; }
td:nth-of-type(2):before { content: "REGION_NAME"; }
  }
  
  /* Smartphones (portrait and landscape) ----------- */
  @media only screen
  and (min-device-width : 320px)
  and (max-device-width : 480px) {
    body { 
      padding: 0; 
      margin: 0; 
      width: 320px; }
    }
  
  /* iPads (portrait and landscape) ----------- */
  @media only screen and (min-device-width: 768px) and (max-device-width: 1024px) {
    body { 
      width: 495px; 
    }
  }
  
  </style>
  <!--<![endif]-->
<script type="text/javascript">

function search(){
  
  var s = document.getElementById('search').value;

  rows = document.getElementById('data').getElementsByTagName('TR');
  for(var i=0;i<rows.length;i++){
    if ( rows[i].textContent.indexOf(s)>0  || s.length==0 ) {
	  rows[i].style.display ='';
    } else {
      rows[i].style.display ='none';
    }
  }
}


var timer;
function delayedSearch() {
	clearTimeout(timer);
	console.log('delay-ing')
    timer = setTimeout(function () {
		console.log('delay-running')
		search();
    }, 500);
  }</script>
</head>

<body>
<div><input type="text" size="30" maxlength="1000" value="" id="search" onkeyup="delayedSearch();" /><input type="button" value="Go" onclick="lsearch();"/> </div>
<table><thead><tr>	<th>REGION_ID</th>
	<th>REGION_NAME</th>
</tr></thead>
<tbody id="data">

	<tr>
<td align="right">1</td>
<td>Europe</td>
	</tr>
	<tr>
<td align="right">2</td>
<td>Americas</td>
	</tr>
	<tr>
<td align="right">3</td>
<td>Asia</td>
	</tr>
	<tr>
<td align="right">4</td>
<td>Middle East and Africa</td>
	</tr>
</tbody></table><!-- SQL:
SELECT * FROM regions--></body></html>