SQLclの出力フォーマットを試す（Oracle Database 12c Release 2)

SQLclユーティリティからSELECT文を実行した時の出力フォーマットを変更してみます。SQL*PlusではSET MARKUPコマンドでCSVとHTMLを指定できますが、SQLclではそれ以外にも面白い構文が利用できます。

SQLcl

SQLclはSQL*Plusと同様にOracle Databaseに接続し、SQL文を実行するためのフロントエンド・ツールです。Javaで作られたアプリケーションで、実行するとJava VMが起動します。
元々はOracle SQL Developerと一緒に配布されていましたが、Oracle Database 12c Release 2には同梱され、標準インストールされます。

最新のSQLclコマンドはSQL Developer Command Line からダウンロードできます。

SQLclの起動コマンドはsqlです（ORACLE_HOME/bin/sql）。このコマンドを実行すると、ORACLE_HOME/sqldeveloper/sqlcl/bin/sqlコマンドが起動されます。このシェル・スクリプトは内部でjavaコマンドを実行します。
起動時のパラメーター指定方法はSQL*Plusと同じです。

$ sql / as sysdba

SQLcl: 金 5 18 14:00:05 2018のリリース12.2.0.1.0 RC

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

接続先:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL>

SQLclの起動メッセージのエンコードは環境変数NLS_LANGではなく、LANGで指定したコードが使用されます。

SELECT文の出力フォーマット

DEFAULT (デフォルト値)

SELECT文の出力フォーマットを指定するパラメーターはSQLFORMATです。このパラメーターに出力フォーマットを指定します。
デフォルト値はDEFAULTで、SQL*Plusと同様のフォーマットで出力されます。DATE型はNLS_DATE_FORMATで指定されたフォーマットで出力されます。

SQL> SET LINE 100
SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17        800                    20
      7499 ALLEN      SALESMAN        7698 81-02-20       1600        300         30
      7521 WARD       SALESMAN        7698 81-02-22       1250        500         30
      7566 JONES      MANAGER         7839 81-04-02       2975                    20
      7654 MARTIN     SALESMAN        7698 81-09-28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81-05-01       2850                    30
      7782 CLARK      MANAGER         7839 81-06-09       2450                    10
      7788 SCOTT      ANALYST         7566 87-04-19       3000                    20
      7839 KING       PRESIDENT            81-11-17       5000                    10
      7844 TURNER     SALESMAN        7698 81-09-08       1500          0         30
      7876 ADAMS      CLERK           7788 87-05-23       1100                    20
      7900 JAMES      CLERK           7698 81-12-03        950                    30
      7902 FORD       ANALYST         7566 81-12-03       3000                    20
      7934 MILLER     CLERK           7782 82-01-23       1300                    10

14行が選択されました。

SQL>

CSV

SQLFORMATにCSVを指定すると、SELECT文の結果がCSV形式で出力されます。

SQL> SET SQLFORMAT CSV
SQL> SELECT * FROM EMP;
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"SMITH","CLERK",7902,80-12-17,800,,20
7499,"ALLEN","SALESMAN",7698,81-02-20,1600,300,30
7521,"WARD","SALESMAN",7698,81-02-22,1250,500,30
7566,"JONES","MANAGER",7839,81-04-02,2975,,20
7654,"MARTIN","SALESMAN",7698,81-09-28,1250,1400,30
7698,"BLAKE","MANAGER",7839,81-05-01,2850,,30
7782,"CLARK","MANAGER",7839,81-06-09,2450,,10
7788,"SCOTT","ANALYST",7566,87-04-19,3000,,20
7839,"KING","PRESIDENT",,81-11-17,5000,,10
7844,"TURNER","SALESMAN",7698,81-09-08,1500,0,30
7876,"ADAMS","CLERK",7788,87-05-23,1100,,20
7900,"JAMES","CLERK",7698,81-12-03,950,,30
7902,"FORD","ANALYST",7566,81-12-03,3000,,20
7934,"MILLER","CLERK",7782,82-01-23,1300,,10

14行が選択されました。

列区切りはCOLSEPパラメーターとは無関係にカンマ（,）で区切られます。文字列型はダブル・クオーテーションで囲まれます。
文字列型の中にダブル・クオーテーションが含まれる場合は、ダブル・クオーテーションが２重に出力される仕様です。
先頭行はSET TITLE OFFコマンドで出力を抑制できます。またSET FEEDBACK OFFコマンドも併用すると完全なCSV形式で出力されます。

INSERT

SQLFORMATにinsertを指定すると、SELECT文の結果がinsert文の形式で出力されます。小規模なデータを他のデータベースに移行する場合に便利な機能です。

SQL> SET SQLFORMAT INSERT
SQL> SELECT * FROM emp;
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('80-12-17','RR-MM-DD HH24:MI:SSXFF'),800,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_timestamp('81-02-20','RR-MM-DD HH24:MI:SSXFF'),1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_timestamp('81-02-22','RR-MM-DD HH24:MI:SSXFF'),1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('81-04-02','RR-MM-DD HH24:MI:SSXFF'),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_timestamp('81-09-28','RR-MM-DD HH24:MI:SSXFF'),1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_timestamp('81-05-01','RR-MM-DD HH24:MI:SSXFF'),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_timestamp('81-06-09','RR-MM-DD HH24:MI:SSXFF'),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_timestamp('87-04-19','RR-MM-DD HH24:MI:SSXFF'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_timestamp('81-11-17','RR-MM-DD HH24:MI:SSXFF'),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_timestamp('81-09-08','RR-MM-DD HH24:MI:SSXFF'),1500,0,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_timestamp('87-05-23','RR-MM-DD HH24:MI:SSXFF'),1100,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_timestamp('81-12-03','RR-MM-DD HH24:MI:SSXFF'),950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('81-12-03','RR-MM-DD HH24:MI:SSXFF'),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_timestamp('82-01-23','RR-MM-DD HH24:MI:SSXFF'),1300,null,10);

14行が選択されました。

LOADER

SQLFORMATにloaderを指定すると、SELECT文の結果がSQL*Loaderのローディングに便利な形式で出力されます。
タイトルは出力されず、セパレータとして|が出力されます。文字列型や日付型はダブルクオーテーションで囲まれ、DATE型の列は秒まで出力されます。

SQL> SET SQLFORMAT LOADER
SQL> SELECT * FROM emp;
 7369|"SMITH"|"CLERK"|7902|"1980-12-17 00:00:00"|800||20|
 7499|"ALLEN"|"SALESMAN"|7698|"1981-02-20 00:00:00"|1600|300|30|
 7521|"WARD"|"SALESMAN"|7698|"1981-02-22 00:00:00"|1250|500|30|
 7566|"JONES"|"MANAGER"|7839|"1981-04-02 00:00:00"|2975||20|
 7654|"MARTIN"|"SALESMAN"|7698|"1981-09-28 00:00:00"|1250|1400|30|
 7698|"BLAKE"|"MANAGER"|7839|"1981-05-01 00:00:00"|2850||30|
 7782|"CLARK"|"MANAGER"|7839|"1981-06-09 00:00:00"|2450||10|
 7788|"SCOTT"|"ANALYST"|7566|"1987-04-19 00:00:00"|3000||20|
 7839|"KING"|"PRESIDENT"||"1981-11-17 00:00:00"|5000||10|
 7844|"TURNER"|"SALESMAN"|7698|"1981-09-08 00:00:00"|1500|0|30|
 7876|"ADAMS"|"CLERK"|7788|"1987-05-23 00:00:00"|1100||20|
 7900|"JAMES"|"CLERK"|7698|"1981-12-03 00:00:00"|950||30|
 7902|"FORD"|"ANALYST"|7566|"1981-12-03 00:00:00"|3000||20|
 7934|"MILLER"|"CLERK"|7782|"1982-01-23 00:00:00"|1300||10|

HTML

SQLFORMATにHTMLを指定すると、SELECT文の結果がHTML形式で出力されます。

SQL> SET SQLFORMAT HTML
SQL> SELECT * FROM emp;
<!DOCTYPE html>
<html>

<head>
  <meta charset='UTF8'>

  <title>Responsive Table</title>

  <meta name="viewport" content="width=device-width, initial-scale=1.0">


  <style>
  * {
    margin: 0;
    padding: 0;
  }

...

<table><thead><tr>      <th>EMPNO</th>
        <th>ENAME</th>
        <th>JOB</th>
        <th>MGR</th>
        <th>HIREDATE</th>
        <th>SAL</th>
        <th>COMM</th>
        <th>DEPTNO</th>
</tr></thead>
<tbody id="data">

        <tr>
<td align="right">7369</td>
<td>SMITH</td>
<td>CLERK</td>
<td align="right">7902</td>
<td>80-12-17</td>
<td align="right">800</td>
<td align="right">&nbsp;</td>
<td align="right">20</td>
        </tr>
        <tr>
...

XML

SQLFORMATにXMLを指定すると、SELECT文の結果がXML形式で出力されます。

RESULTSタグで囲まれた検索結果が ROWタグ、COLUMNタグ を使ってXML化されます。エンコードは環境変数に依存せずUTF-8になるようです。タイトル行は出力されません。

SQL> SET SQLFORMAT XML
SQL> SELECT * FROM emp;
<?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[80-12-17]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[800]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="EMPNO"><![CDATA[7499]]></COLUMN>
                <COLUMN NAME="ENAME"><![CDATA[ALLEN]]></COLUMN>
                <COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
                <COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
                <COLUMN NAME="HIREDATE"><![CDATA[81-02-20]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[1600]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[300]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="EMPNO"><![CDATA[7521]]></COLUMN>
                <COLUMN NAME="ENAME"><![CDATA[WARD]]></COLUMN>
                <COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
                <COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
                <COLUMN NAME="HIREDATE"><![CDATA[81-02-22]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[1250]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[500]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[30]]></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[81-04-02]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[2975]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="EMPNO"><![CDATA[7654]]></COLUMN>
                <COLUMN NAME="ENAME"><![CDATA[MARTIN]]></COLUMN>
                <COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
                <COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
                <COLUMN NAME="HIREDATE"><![CDATA[81-09-28]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[1250]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[1400]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="EMPNO"><![CDATA[7698]]></COLUMN>
                <COLUMN NAME="ENAME"><![CDATA[BLAKE]]></COLUMN>
                <COLUMN NAME="JOB"><![CDATA[MANAGER]]></COLUMN>
                <COLUMN NAME="MGR"><![CDATA[7839]]></COLUMN>
                <COLUMN NAME="HIREDATE"><![CDATA[81-05-01]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[2850]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[30]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="EMPNO"><![CDATA[7782]]></COLUMN>
                <COLUMN NAME="ENAME"><![CDATA[CLARK]]></COLUMN>
                <COLUMN NAME="JOB"><![CDATA[MANAGER]]></COLUMN>
                <COLUMN NAME="MGR"><![CDATA[7839]]></COLUMN>
                <COLUMN NAME="HIREDATE"><![CDATA[81-06-09]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[2450]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[10]]></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[87-04-19]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[3000]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="EMPNO"><![CDATA[7839]]></COLUMN>
                <COLUMN NAME="ENAME"><![CDATA[KING]]></COLUMN>
                <COLUMN NAME="JOB"><![CDATA[PRESIDENT]]></COLUMN>
                <COLUMN NAME="MGR"><![CDATA[]]></COLUMN>
                <COLUMN NAME="HIREDATE"><![CDATA[81-11-17]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[5000]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[10]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="EMPNO"><![CDATA[7844]]></COLUMN>
                <COLUMN NAME="ENAME"><![CDATA[TURNER]]></COLUMN>
                <COLUMN NAME="JOB"><![CDATA[SALESMAN]]></COLUMN>
                <COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
                <COLUMN NAME="HIREDATE"><![CDATA[81-09-08]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[1500]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[0]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[30]]></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[87-05-23]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[1100]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="EMPNO"><![CDATA[7900]]></COLUMN>
                <COLUMN NAME="ENAME"><![CDATA[JAMES]]></COLUMN>
                <COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
                <COLUMN NAME="MGR"><![CDATA[7698]]></COLUMN>
                <COLUMN NAME="HIREDATE"><![CDATA[81-12-03]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[950]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[30]]></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[81-12-03]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[3000]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="EMPNO"><![CDATA[7934]]></COLUMN>
                <COLUMN NAME="ENAME"><![CDATA[MILLER]]></COLUMN>
                <COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
                <COLUMN NAME="MGR"><![CDATA[7782]]></COLUMN>
                <COLUMN NAME="HIREDATE"><![CDATA[82-01-23]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[1300]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[10]]></COLUMN>
        </ROW>
</RESULTS>
14行が選択されました。

SQL>

JSON

SQLFORMATにjsonを指定すると、SELECT文の結果がjson形式で出力されます。

SQL> SET SQLFORMAT json
SQL> SELECT * FROM emp;
{"results":[{"columns":[{"name":"EMPNO","type":"NUMBER"},{"name":"ENAME","type":"NUMBER"},{"name":"JOB","type":"NUMBER"},{"name":"MGR","type":"NUMBER"},{"name":"HIREDATE","type":"NUMBER"},{"name":"SAL","type":"NUMBER"},{"name":"COMM","type":"NUMBER"},{"name":"DEPTNO","type":"NUMBER"}],"items":
[
{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"80-12-17","sal":800,"deptno":20},{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"81-02-20","sal":1600,"comm":300,"deptno":30},{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"81-02-22","sal":1250,"comm":500,"deptno":30},{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"81-04-02","sal":2975,"deptno":20},{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"81-09-28","sal":1250,"comm":1400,"deptno":30},{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"81-05-01","sal":2850,"deptno":30},{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"81-06-09","sal":2450,"deptno":10},{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"87-04-19","sal":3000,"deptno":20},{"empno":7839,"ename":"KING","job":"PRESIDENT","hiredate":"81-11-17","sal":5000,"deptno":10},{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"81-09-08","sal":1500,"comm":0,"deptno":30},{"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"87-05-23","sal":1100,"deptno":20},{"empno":7900,"ename":"JAMES","job":"CLERK","mgr":7698,"hiredate":"81-12-03","sal":950,"deptno":30},{"empno":7902,"ename":"FORD","job":"ANALYST","mgr":7566,"hiredate":"81-12-03","sal":3000,"deptno":20},{"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"82-01-23","sal":1300,"deptno":10}]}]}
14行が選択されました。

SQL>

ANSICONSOLE

SQLFORMATにansiconsoleを指定すると、SELECT文の結果がコンソール端末用フォーマットで出力されます。
列名の出力にエスケープ・シーケンスが含まれます。

SQL> SET SQLFORMAT ansiconsole
SQL> SELECT * FROM emp;
EMPNO  ENAME   JOB        MGR   HIREDATE  SAL   COMM  DEPTNO
7369   SMITH   CLERK      7902  80-12-17  800         20
7499   ALLEN   SALESMAN   7698  81-02-20  1600  300   30
7521   WARD    SALESMAN   7698  81-02-22  1250  500   30
7566   JONES   MANAGER    7839  81-04-02  2975        20
7654   MARTIN  SALESMAN   7698  81-09-28  1250  1400  30
7698   BLAKE   MANAGER    7839  81-05-01  2850        30
7782   CLARK   MANAGER    7839  81-06-09  2450        10
7788   SCOTT   ANALYST    7566  87-04-19  3000        20
7839   KING    PRESIDENT        81-11-17  5000        10
7844   TURNER  SALESMAN   7698  81-09-08  1500  0     30
7876   ADAMS   CLERK      7788  87-05-23  1100        20
7900   JAMES   CLERK      7698  81-12-03  950         30
7902   FORD    ANALYST    7566  81-12-03  3000        20
7934   MILLER  CLERK      7782  82-01-23  1300        10


14行が選択されました。

FIXED

SQLFORMATにfixedを指定すると、SELECT文の結果が固定幅で出力されます。各列値はダブル・クオーテーションで囲まれます。

SQL> SET SQLFORMAT fixed
SQL> SELECT * FROM emp;
"EMPNO"                       "ENAME"                       "JOB"                         "MGR"                         "HIREDATE"                    "SAL"                         "COMM"                        "DEPTNO"
"7369"                        "SMITH"                       "CLERK"                       "7902"                        "80-12-17"                    "800"                         ""                            "20"
"7499"                        "ALLEN"                       "SALESMAN"                    "7698"                        "81-02-20"                    "1600"                        "300"                         "30"
"7521"                        "WARD"                        "SALESMAN"                    "7698"                        "81-02-22"                    "1250"                        "500"                         "30"
"7566"                        "JONES"                       "MANAGER"                     "7839"                        "81-04-02"                    "2975"                        ""                            "20"
"7654"                        "MARTIN"                      "SALESMAN"                    "7698"                        "81-09-28"                    "1250"                        "1400"                        "30"
"7698"                        "BLAKE"                       "MANAGER"                     "7839"                        "81-05-01"                    "2850"                        ""                            "30"
"7782"                        "CLARK"                       "MANAGER"                     "7839"                        "81-06-09"                    "2450"                        ""                            "10"
"7788"                        "SCOTT"                       "ANALYST"                     "7566"                        "87-04-19"                    "3000"                        ""                            "20"
"7839"                        "KING"                        "PRESIDENT"                   ""                            "81-11-17"                    "5000"                        ""                            "10"
"7844"                        "TURNER"                      "SALESMAN"                    "7698"                        "81-09-08"                    "1500"                        "0"                           "30"
"7876"                        "ADAMS"                       "CLERK"                       "7788"                        "87-05-23"                    "1100"                        ""                            "20"
"7900"                        "JAMES"                       "CLERK"                       "7698"                        "81-12-03"                    "950"                         ""                            "30"
"7902"                        "FORD"                        "ANALYST"                     "7566"                        "81-12-03"                    "3000"                        ""                            "20"
"7934"                        "MILLER"                      "CLERK"                       "7782"                        "82-01-23"                    "1300"                        ""                            "10"

14行が選択されました。

SQL>

カスタムフォーマット

SET SQLFORMAT DELIMITEDコマンドを実行すると、特殊なフォーマットで出力できます。構文は「SET SQLFORMAT DELIMITED 列区切り 左囲み文字 右囲み文字」です。

SQL> SET SQLFORMAT DELIMITED * " '
SQL> SELECT * FROM emp;
"EMPNO'*"ENAME'*"JOB'*"MGR'*"HIREDATE'*"SAL'*"COMM'*"DEPTNO'
7369*"SMITH'*"CLERK'*7902*80-12-17*800**20
7499*"ALLEN'*"SALESMAN'*7698*81-02-20*1600*300*30
7521*"WARD'*"SALESMAN'*7698*81-02-22*1250*500*30
7566*"JONES'*"MANAGER'*7839*81-04-02*2975**20
7654*"MARTIN'*"SALESMAN'*7698*81-09-28*1250*1400*30
7698*"BLAKE'*"MANAGER'*7839*81-05-01*2850**30
7782*"CLARK'*"MANAGER'*7839*81-06-09*2450**10
7788*"SCOTT'*"ANALYST'*7566*87-04-19*3000**20
7839*"KING'*"PRESIDENT'**81-11-17*5000**10
7844*"TURNER'*"SALESMAN'*7698*81-09-08*1500*0*30
7876*"ADAMS'*"CLERK'*7788*87-05-23*1100**20
7900*"JAMES'*"CLERK'*7698*81-12-03*950**30
7902*"FORD'*"ANALYST'*7566*81-12-03*3000**20
7934*"MILLER'*"CLERK'*7782*82-01-23*1300**10

14行が選択されました。

SQL>