SQLcl – Using SQLFORMAT

One of the new settings in SQLcl is the ability to set different output formats when running queries. You can access this via the set command –


SQL> help set
SET
---

Sets a system variable to alter the SQLcl environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page

SET system_variable value

where system_variable and value represent one of the following clauses:

help set <setting> for more details

APPINFO
ARRAYSIZE
AUTOCOMMIT
AUTOPRINT
AUTORECOVERY
AUTOTRACE

BLOCKTERMINATOR
CLEAR
CMDSEP
COLSEP
CONCAT
COPYCOMMIT
COPYTYPECHECK
DEFINE
ECHO
EDITF[ILE]
EMBEDDED
ENCODING
ESCAPE
ESCCHAR
EXITCOMMIT
FEEDBACK
FLUSH
HEADING
HEADSEP
LDAPCON
LINESIZE
LONG

LONGCHUNKSIZE
NET
NEWPAGE
NOVERWRITE
NULL
NUMFORMAT
NUMWIDTH
PAGESIZE
PAUSE
RECSEPCHAR
SERVEROUTPUT
SHOWMODE
SQLBLANKLINES
SQLCASE
SQLCONTINUE
SQLFORMAT  <------------ Here it is!
SQLPLUSCOMPATIBILITY
SQLPREFIX
SQLPROMPT
SUFFIX
TAB
TERMOUT
TIME
TIMING
TRIMOUT
TRIMSPOOL
VERIFY
WRAP

Viewing the command specific help shows the available output formats


SQL> help set sqlformat
SET SQLFORMAT
SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}

So, let’s try a couple of tests against the ubiquitous emp table, firstly lets query it without making any changes to the SQLFORMAT settings

 

We can view what the current setting is via the show command


SQL> show sqlformat
SQL Format : Default

Let’s try the JSON format

 

As you can imagine the CSV format does exactly what you’d expect

You can imagine the previous formats are very useful combined with a SPOOL command to save the data to a file on the filesystem in a particular format.

The most common format I use is ANSICONSOLE (which works very well with a large monitor), which does a great job at auto-sizing the column sizes and always displaying the full column name (instead of a shorten abbreviated name).

As you can see, using SQLFORMAT is an extremely useful way to format your SQL query output

One thought on “SQLcl – Using SQLFORMAT

  1. Pingback: SQLcl – do REPEAT yourself | Johns Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s