DBA Hub

📋Steps in this guide1/12

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.

oracle miscconfigurationintermediate
by OracleDba
20 views
1

Setup

The examples in this article require the following table. Feedback is turned off for the following queries, so the number of rows produced is not displayed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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;

SQL> SET FEEDBACK OFF
2

Comment Shortcuts

The sections below describe how to use 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 setting directly.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
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;
3

DEFAULT

The option clears all previous settings and returns to the default output.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>
4

CSV

The format produces standard Comma-Separated Variable output, with string values enclosed by double-quotes. The first line contains the column names.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
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>
5

HTML

The 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. The output from this query is quite large, so you can see the resulting HTML in the sqlcl-emp.htm file.

Code/Command (click line numbers to comment):

1
2
3
SQL>
SET SQLFORMAT HTML
SQL> SELECT * FROM emp WHERE deptno = 20;
6

XML

The format produces a tag-based XML document. All data is presented as tags.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
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>
7

JSON

The 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 format pretty-prints the JSON, so it is human readable.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
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>
8

ANSICONSOLE

The 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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>
9

INSERT

The format produces the statements that could be used to recreate the rows in a table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
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>
10

LOADER

The format produces pipe delimited output with string values enclosed by double-quotes. The column names are not included in the output.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
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>
11

FIXED

The format produces fixed width columns with all data enclosed by double-quotes. The first line contains the column names.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
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>
12

DELIMITED

The 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. In the folowing example the delimiter string is ~del~ and string values are enclosed in double-quotes. For more information see: - Formatting Query Results - SQLcl : Format Query Results - SQLcl : All Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
SET SQLFORMAT DELIMITED <delimiter> <left enclosure> <right enclosure>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!