How to Format Query Output in SQLcl (SET SQLFORMAT)

SQLcl, Oracle's modern command-line tool, can print query results in many formats besides the classic columns: CSV, JSON, INSERT statements, delimited text, and a readable console table. SET SQLFORMAT switches between them, which turns any query into an export without extra code.

Code for This Guide

The main examples are in the examples/tools folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

set sqlformat csv | json | json-formatted | xml | html | insert | loader
            | delimited separator | fixed | text | ansiconsole | default

The setting applies to every query that follows in the session, until it is changed.

CSV, JSON, and INSERT

Example:

set sqlformat csv
select airport_code, city, elevation_ft from airports where country_code = 'IN';

set sqlformat json
select airport_code, city from airports where country_code = 'AE';

set sqlformat insert
select country_code, country_name from countries where country_code = 'NP';

Output:

"AIRPORT_CODE","CITY","ELEVATION_FT"
"BOM","Mumbai",39
"DEL","Delhi",777
"BLR","Bengaluru",3002

{"results":[{"columns":[{"name":"AIRPORT_CODE","type":"CHAR"},{"name":"CITY","type":"VARCHAR2"}],"items":
[
{"airport_code":"DXB","city":"Dubai"}
]}]}
REM INSERTING into COUNTRIES
SET DEFINE OFF;
Insert into COUNTRIES (COUNTRY_CODE,COUNTRY_NAME) values ('NP','Nepal');

• CSV quotes text and writes a header line with the column names.
• JSON writes the columns with their types, then one object per row.
• INSERT writes statements that recreate the rows, ready for a script.

Delimited, Loader, and Console

Example:

set sqlformat delimited |
select airport_code, city from airports where country_code = 'GB';

set sqlformat loader
select airport_code, city from airports where country_code = 'GB';

set sqlformat ansiconsole
select airport_code, city from airports where country_code = 'GB';

Output:

"AIRPORT_CODE"|"CITY"
"LHR"|"London"

 "LHR"|"London"|

AIRPORT_CODE    CITY
_______________ _________
LHR             London

DELIMITED takes a separator, here a pipe. LOADER writes data for SQL*Loader without a header. ANSICONSOLE, the format the examples in these guides use, sizes columns to their content.

Things to Know

• Combine SET SQLFORMAT with SPOOL to write the output to a file.
• SET SQLFORMAT DEFAULT returns to the classic SQL*Plus-style layout.
• A comment hint such as /*csv*/ after SELECT formats one query only: select /*csv*/ * from airports.

Related Guides

• How to Get DDL with DBMS_METADATA.GET_DDL
• How to Write a CLOB to a File with DBMS_LOB.CLOB2FILE

Conclusion

SET SQLFORMAT makes SQLcl print query results as CSV, JSON, INSERT statements, delimited text, and more. Pick the format, spool to a file, and any query becomes an export.