8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
SQLcl : Format Query Results with the SET SQLFORMAT Command
SQLcl makes it simple to format query results using the SET SQLFORMAT command and a variety of built-in formats.
Related articles.
Setup
The examples in this article require the following table.
CREATE TABLE EMP (
EMPNO NUMBER(4,0),
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0),
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;
Feedback is turned off for the following queries, so the number of rows produced is not displayed.
SQL> SET FEEDBACK OFF
Comment Shortcuts
The sections below describe how to use SQLFORMAT setting in SQLcl, but there is an alternative solution to get the same results. The following comments give similar results, without having to alter the SQLFORMAT setting directly.
SELECT /*csv*/ * FROM emp; SELECT /*html*/ * FROM emp; SELECT /*xml*/ * FROM emp; SELECT /*json*/ * FROM emp; SELECT /*json-formatted*/ * FROM emp; SELECT /*ansiconsole*/ * FROM emp; SELECT /*insert*/ * FROM emp; SELECT /*loader*/ * FROM emp; SELECT /*fixed*/ * FROM emp; SELECT /*delimited*/ * FROM emp; SELECT /*text*/ * FROM emp;
DEFAULT
The DEFAULT option clears all previous SQLFORMAT settings and returns to the default output.
SQL> SET SQLFORMAT DEFAULT
SQL Format Cleared
SQL> SELECT * FROM emp WHERE deptno = 20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
SQL>
CSV
The CSV format produces standard Comma-Separated Variable output, with string values enclosed by double-quotes. The first line contains the column names.
SQL> SET SQLFORMAT CSV SQL> SELECT * FROM emp WHERE deptno = 20; "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" 7369,"SMITH","CLERK",7902,17-DEC-80,800,,20 7566,"JONES","MANAGER",7839,02-APR-81,2975,,20 7788,"SCOTT","ANALYST",7566,19-APR-87,3000,,20 7876,"ADAMS","CLERK",7788,23-MAY-87,1100,,20 7902,"FORD","ANALYST",7566,03-DEC-81,3000,,20 SQL>
HTML
The HTML format produces the HTML for a responsive table. The contents of the table dynamically alter to match the search string entered in the top-left text field.
SQL> SET SQLFORMAT HTML SQL> SELECT * FROM emp WHERE deptno = 20;
The output from this query is quite large, so you can see the resulting HTML in the sqlcl-emp.htm file.
XML
The XML format produces a tag-based XML document. All data is presented as CDATA tags.
SQL> SET SQLFORMAT XML
SQL> SELECT * FROM emp WHERE deptno = 20;
<?xml version='1.0' encoding='UTF8' ?>
<RESULTS>
<ROW>
<COLUMN NAME="EMPNO"><![CDATA[7369]]></COLUMN>
<COLUMN NAME="ENAME"><![CDATA[SMITH]]></COLUMN>
<COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
<COLUMN NAME="MGR"><![CDATA[7902]]></COLUMN>
<COLUMN NAME="HIREDATE"><![CDATA[17-DEC-80]]></COLUMN>
<COLUMN NAME="SAL"><![CDATA[800]]></COLUMN>
<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
</ROW>
<ROW>
<COLUMN NAME="EMPNO"><![CDATA[7566]]></COLUMN>
<COLUMN NAME="ENAME"><![CDATA[JONES]]></COLUMN>
<COLUMN NAME="JOB"><![CDATA[MANAGER]]></COLUMN>
<COLUMN NAME="MGR"><![CDATA[7839]]></COLUMN>
<COLUMN NAME="HIREDATE"><![CDATA[02-APR-81]]></COLUMN>
<COLUMN NAME="SAL"><![CDATA[2975]]></COLUMN>
<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
</ROW>
<ROW>
<COLUMN NAME="EMPNO"><![CDATA[7788]]></COLUMN>
<COLUMN NAME="ENAME"><![CDATA[SCOTT]]></COLUMN>
<COLUMN NAME="JOB"><![CDATA[ANALYST]]></COLUMN>
<COLUMN NAME="MGR"><![CDATA[7566]]></COLUMN>
<COLUMN NAME="HIREDATE"><![CDATA[19-APR-87]]></COLUMN>
<COLUMN NAME="SAL"><![CDATA[3000]]></COLUMN>
<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
</ROW>
<ROW>
<COLUMN NAME="EMPNO"><![CDATA[7876]]></COLUMN>
<COLUMN NAME="ENAME"><![CDATA[ADAMS]]></COLUMN>
<COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
<COLUMN NAME="MGR"><![CDATA[7788]]></COLUMN>
<COLUMN NAME="HIREDATE"><![CDATA[23-MAY-87]]></COLUMN>
<COLUMN NAME="SAL"><![CDATA[1100]]></COLUMN>
<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
</ROW>
<ROW>
<COLUMN NAME="EMPNO"><![CDATA[7902]]></COLUMN>
<COLUMN NAME="ENAME"><![CDATA[FORD]]></COLUMN>
<COLUMN NAME="JOB"><![CDATA[ANALYST]]></COLUMN>
<COLUMN NAME="MGR"><![CDATA[7566]]></COLUMN>
<COLUMN NAME="HIREDATE"><![CDATA[03-DEC-81]]></COLUMN>
<COLUMN NAME="SAL"><![CDATA[3000]]></COLUMN>
<COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
<COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
</ROW>
</RESULTS>
SQL>
JSON
The JSON format produces a JSON document containing the definitions of the columns along with the data they contain. The output is minified, so it is compact, but hard to read. The JSON-FORMATTED format pretty-prints the JSON, so it is human readable.
SQL> SET SQLFORMAT JSON-FORMATTED
SQL> SELECT * FROM emp WHERE deptno = 20;
{
"results" : [
{
"columns" : [
{
"name" : "EMPNO",
"type" : "NUMBER"
},
{
"name" : "ENAME",
"type" : "VARCHAR2"
},
{
"name" : "JOB",
"type" : "VARCHAR2"
},
{
"name" : "MGR",
"type" : "NUMBER"
},
{
"name" : "HIREDATE",
"type" : "DATE"
},
{
"name" : "SAL",
"type" : "NUMBER"
},
{
"name" : "COMM",
"type" : "NUMBER"
},
{
"name" : "DEPTNO",
"type" : "NUMBER"
}
],
"items" : [
{
"empno" : 7369,
"ename" : "SMITH",
"job" : "CLERK",
"mgr" : 7902,
"hiredate" : "17-DEC-80",
"sal" : 800,
"comm" : "",
"deptno" : 20
},
{
"empno" : 7499,
"ename" : "ALLEN",
"job" : "SALESMAN",
"mgr" : 7698,
"hiredate" : "20-FEB-81",
"sal" : 1600,
"comm" : 300,
"deptno" : 30
},
{
"empno" : 7521,
"ename" : "WARD",
"job" : "SALESMAN",
"mgr" : 7698,
"hiredate" : "22-FEB-81",
"sal" : 1250,
"comm" : 500,
"deptno" : 30
},
{
"empno" : 7566,
"ename" : "JONES",
"job" : "MANAGER",
"mgr" : 7839,
"hiredate" : "02-APR-81",
"sal" : 2975,
"comm" : "",
"deptno" : 20
},
{
"empno" : 7654,
"ename" : "MARTIN",
"job" : "SALESMAN",
"mgr" : 7698,
"hiredate" : "28-SEP-81",
"sal" : 1250,
"comm" : 1400,
"deptno" : 30
},
{
"empno" : 7698,
"ename" : "BLAKE",
"job" : "MANAGER",
"mgr" : 7839,
"hiredate" : "01-MAY-81",
"sal" : 2850,
"comm" : "",
"deptno" : 30
},
{
"empno" : 7782,
"ename" : "CLARK",
"job" : "MANAGER",
"mgr" : 7839,
"hiredate" : "09-JUN-81",
"sal" : 2450,
"comm" : "",
"deptno" : 10
},
{
"empno" : 7788,
"ename" : "SCOTT",
"job" : "ANALYST",
"mgr" : 7566,
"hiredate" : "19-APR-87",
"sal" : 3000,
"comm" : "",
"deptno" : 20
},
{
"empno" : 7839,
"ename" : "KING",
"job" : "PRESIDENT",
"mgr" : "",
"hiredate" : "17-NOV-81",
"sal" : 5000,
"comm" : "",
"deptno" : 10
},
{
"empno" : 7844,
"ename" : "TURNER",
"job" : "SALESMAN",
"mgr" : 7698,
"hiredate" : "08-SEP-81",
"sal" : 1500,
"comm" : 0,
"deptno" : 30
},
{
"empno" : 7876,
"ename" : "ADAMS",
"job" : "CLERK",
"mgr" : 7788,
"hiredate" : "23-MAY-87",
"sal" : 1100,
"comm" : "",
"deptno" : 20
},
{
"empno" : 7900,
"ename" : "JAMES",
"job" : "CLERK",
"mgr" : 7698,
"hiredate" : "03-DEC-81",
"sal" : 950,
"comm" : "",
"deptno" : 30
},
{
"empno" : 7902,
"ename" : "FORD",
"job" : "ANALYST",
"mgr" : 7566,
"hiredate" : "03-DEC-81",
"sal" : 3000,
"comm" : "",
"deptno" : 20
},
{
"empno" : 7934,
"ename" : "MILLER",
"job" : "CLERK",
"mgr" : 7782,
"hiredate" : "23-JAN-82",
"sal" : 1300,
"comm" : "",
"deptno" : 10
}
]
}
]
}
SQL>
ANSICONSOLE
The ANSICONSOLE format resizes the columns to the width of the data to save space. It also underlines the columns,
rather than using a separate line of output. The underlines don't copy to flat text, so I've manually added the underlines to show you what it looks like in a terminal screen.
SQL> SET SQLFORMAT ANSICONSOLE SQL> SELECT * FROM emp WHERE deptno = 20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-DEC-80 800 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 SQL>
INSERT
The INSERT format produces the INSERT statements that could be used to recreate the rows in a table.
SQL> SET SQLFORMAT INSERT
SQL> SELECT * FROM emp WHERE deptno = 20;
REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH24.MI.SSXFF'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('02-APR-81','DD-MON-RR HH24.MI.SSXFF'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_timestamp('19-APR-87','DD-MON-RR HH24.MI.SSXFF'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_timestamp('23-MAY-87','DD-MON-RR HH24.MI.SSXFF'),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('03-DEC-81','DD-MON-RR HH24.MI.SSXFF'),3000,null,20);
SQL>
LOADER
The LOADER format produces pipe delimited output with string values enclosed by double-quotes. The column names are not included in the output.
SQL> SET SQLFORMAT LOADER SQL> SELECT * FROM emp WHERE deptno = 20; 7369|"SMITH"|"CLERK"|7902|"1980-12-17 00:00:00"|800||20| 7566|"JONES"|"MANAGER"|7839|"1981-04-02 00:00:00"|2975||20| 7788|"SCOTT"|"ANALYST"|7566|"1987-04-19 00:00:00"|3000||20| 7876|"ADAMS"|"CLERK"|7788|"1987-05-23 00:00:00"|1100||20| 7902|"FORD"|"ANALYST"|7566|"1981-12-03 00:00:00"|3000||20| SQL>
FIXED
The FIXED format produces fixed width columns with all data enclosed by double-quotes. The first line contains the column names.
SQL> SET SQLFORMAT FIXED SQL> SELECT * FROM emp WHERE deptno = 20; "EMPNO" "ENAME" "JOB" "MGR" "HIREDATE" "SAL" "COMM" "DEPTNO" "7369" "SMITH" "CLERK" "7902" "17-DEC-80" "800" "" "20" "7566" "JONES" "MANAGER" "7839" "02-APR-81" "2975" "" "20" "7788" "SCOTT" "ANALYST" "7566" "19-APR-87" "3000" "" "20" "7876" "ADAMS" "CLERK" "7788" "23-MAY-87" "1100" "" "20" "7902" "FORD" "ANALYST" "7566" "03-DEC-81" "3000" "" "20" SQL>
DELIMITED
The DELIMITED format is a special case, which allows you to manually define the delimiter string, as well as the characters that will be used to enclose string values. The basic format is as follows.
SET SQLFORMAT DELIMITED <delimiter> <left enclosure> <right enclosure>
In the folowing example the delimiter string is ~del~ and string values are enclosed in double-quotes.
SQL> SET SQLFORMAT DELIMITED ~del~ " " SQL> SELECT * FROM emp WHERE deptno = 20; "EMPNO"~del~"ENAME"~del~"JOB"~del~"MGR"~del~"HIREDATE"~del~"SAL"~del~"COMM"~del~"DEPTNO" 7369~del~"SMITH"~del~"CLERK"~del~7902~del~17-DEC-80~del~800~del~~del~20 7566~del~"JONES"~del~"MANAGER"~del~7839~del~02-APR-81~del~2975~del~~del~20 7788~del~"SCOTT"~del~"ANALYST"~del~7566~del~19-APR-87~del~3000~del~~del~20 7876~del~"ADAMS"~del~"CLERK"~del~7788~del~23-MAY-87~del~1100~del~~del~20 7902~del~"FORD"~del~"ANALYST"~del~7566~del~03-DEC-81~del~3000~del~~del~20 SQL>
For more information see:
Hope this helps. Regards Tim...