8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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

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 I've manually formatted the output below to make it clearer.

SQL> SET SQLFORMAT JSON
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,
               "deptno":20
            },
            {
               "empno":7566,
               "ename":"JONES",
               "job":"MANAGER",
               "mgr":7839,
               "hiredate":"02-APR-81",
               "sal":2975,
               "deptno":20
            },
            {
               "empno":7788,
               "ename":"SCOTT",
               "job":"ANALYST",
               "mgr":7566,
               "hiredate":"19-APR-87",
               "sal":3000,
               "deptno":20
            },
            {
               "empno":7876,
               "ename":"ADAMS",
               "job":"CLERK",
               "mgr":7788,
               "hiredate":"23-MAY-87",
               "sal":1100,
               "deptno":20
            },
            {
               "empno":7902,
               "ename":"FORD",
               "job":"ANALYST",
               "mgr":7566,
               "hiredate":"03-DEC-81",
               "sal":3000,
               "deptno":20
            }
         ]
      }
   ]
}
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...

Back to the Top.