When using SQLcl to run queries against Oracle Database, you can specify the output of the query results using the following two options:

• The SET SQLFORMAT command
• Add a comment shortcut to your query

The SET SQLFORMAT Command

The SET SQLFORMAT command allows you to set how query results are formatted for all subsequent queries in the current session.

To use this option, simply follow SET SQLFORMAT with the desired formatting option.

After that, subsequent queries will be output in that format.

Example:

SET SQLFORMAT ansiconsole;
SELECT * FROM regions;

Result:

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

Here are the options available:

SET SQLFORMAT default;
SET SQLFORMAT ansiconsole;
SET SQLFORMAT fixed;
SET SQLFORMAT csv;
SET SQLFORMAT loader;
SET SQLFORMAT delimited;
SET SQLFORMAT insert;
SET SQLFORMAT json;
SET SQLFORMAT json-formatted;
SET SQLFORMAT xml;
SET SQLFORMAT html;
SET SQLFORMAT text; (undocumented)

You can also run the following command to get a list of these options, along with an explanation of each:

HELP SET SQLFORMAT;

Note that the text option isn’t documented in the help.

Add a Comment Shortcut to your Query

The second option allows you to set the format from within the actual query. To do this, add a comment with your desired format immediately after the SELECT keyword.

Example:

SELECT /*csv*/ * FROM regions;

Result:

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

This option can be used to set the format to any of the formats listed above, except the default option.

Therefore, we can use the following statements to specify the different formatting options for our query output:

SELECT /*ansiconsole*/ * FROM regions;
SELECT /*fixed*/ * FROM regions;
SELECT /*csv*/ * FROM regions;
SELECT /*loader*/ * FROM regions;
SELECT /*delimited*/ * FROM regions;
SELECT /*insert*/ * FROM regions;
SELECT /*json*/ * FROM regions;
SELECT /*json-formatted*/ * FROM regions;
SELECT /*xml*/ * FROM regions;
SELECT /*html*/ * FROM regions;
SELECT /*text*/ * FROM regions;