SET SQLFORMAT on SQLcl 18.1.1

In my previous post, I discussed with you how to installing SQLcl 18.1.1 on your machine or system. Once you have deployed this funny tool on your machine, next you’re necessary to discover some new feature from there. About “SET SQLFORMAT” is a very interesting function. Here’s the operation steps connecting to my Oracle Database test server via SQLcl 18.1.1,

Last login: Wed Jun 13 09:53:17 2018 from xxx.xxx.xxx.xxx
######################################################################################
#                                                                                    #
#     +-------+     +-------        +         +-------/  +            +-------/ (R)  #
#    (         )    |       )      / \        (          |            (              #
#   (           )   |      )      /   \      (           |           (               #
#  (             )  |------      /     \    (            |          (-------/        #
#   (           )   |    \      / ----- \    (           |           (               #
#    (         )    |     \    |         |    (          |       /    (              #
#     +-------+     -      --  -         -    +-------/  +-------+    +-------/      #
#                                                                                    #
#                                                                                    #
#                                              +----------------------------------+  #
#                                              | Applications & Platform Services |  #
#                                              +----------------------------------+  #
#                                                                                    #
######################################################################################
[oracle@test ~]$ . .sqlcl1811
[oracle@test ~]$
[oracle@test ~]$ sql test/test@test

SQLcl: Release 18.1.1 Production on Wed Jun 13 13:45:38 2018

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

show user
USER is "TEST"

help set sqlformat
SET SQLFORMAT
  SET SQLFORMAT {csv,html,xml,json,ansiconsole,insert,loader,fixed,default}

As you can see, SQLFORMAT now supports “csv, html, xml, json, ansiconsole, insert, loader, fixed, default” total 9 number of new functions.

First of all, let’s explore them via creating a test table that generated 1e6 (1,000,000) rows of data.

set timing on

create table test
segment creation immediate
nologging
as
select rownum as id
       , to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime
       , trunc(dbms_random.value(0, 100)) as random_id
       , dbms_random.string('x', 20) random_string
from dual
connect by level <= 1e6
;

Table created.

Elapsed: 00:01:38.74

begin
  dbms_stats.gather_table_stats(
     ownname     => user,
     tabname     => 'TEST',
     method_opt  => 'for all columns size 1'
  );
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.97

Secondly the size of this table “test” is also what my main concern, the following SQL code could achieve this requirement.

select sum(blocks),sum(bytes)/1024/1024 size_mb from user_segments where segment_name = 'TEST';

SUM(BLOCKS)    SIZE_MB
----------- ----------
       7680         60

select blocks,num_rows from user_tables where table_name = 'TEST';

    BLOCKS   NUM_ROWS
---------- ----------
      7590    1000000

Thirdly I’ll use the table “test” previous to respectively demonstrate those interesting functions.

(1) CSV – query data has shown the format with separated comma (the data of type varchar2 encloses with double quotation mark);

set sqlformat csv

set timing on

select * from test where rownum <= 1000000
minus
select * from test where rownum <= 999970
;
"ID","INC_DATETIME","RANDOM_ID","RANDOM_STRING"
999971,"2018-06-12 06:57:26",99,"0KW71P3SRUQRMT44HLN0"
999972,"2018-06-12 06:57:27",32,"KOWDZ7GMTKU3ENC3V71Z"
999973,"2018-06-12 06:57:28",93,"FMSUGGDME5USWEK4NOIO"
999974,"2018-06-12 06:57:29",63,"V9D4POC5HJ3N4F5DQLLL"
999975,"2018-06-12 06:57:30",49,"MG8OIA0LJUW9BJCH7LXW"
999976,"2018-06-12 06:57:31",69,"19AY956P9545VKSO4A77"
999977,"2018-06-12 06:57:32",5,"QUMZLMRBNANVRKQDU8J6"
999978,"2018-06-12 06:57:33",72,"OQG1ZONOEOORB1VWPKSH"
999979,"2018-06-12 06:57:34",36,"4XOV8F08B0WXIRUAMA1F"
999980,"2018-06-12 06:57:35",98,"EMPRO9B6CLO4EKYW270N"
999981,"2018-06-12 06:57:36",58,"B7GLMO6X4K742VMY7ERM"
999982,"2018-06-12 06:57:37",91,"GFGWFT2AAVDMFZ9DXMEV"
999983,"2018-06-12 06:57:38",36,"GNKQDRDNO4G318MANXZ1"
999984,"2018-06-12 06:57:39",30,"Q07DGT8C2S7AH6T7A2I7"
999985,"2018-06-12 06:57:40",75,"EWL8QN6DZX5VKT3EYQU6"
999986,"2018-06-12 06:57:41",87,"YMNX8IOSUOEYWZQUJTS5"
999987,"2018-06-12 06:57:42",50,"YC1DV5J1GYGTEB53WIHV"
999988,"2018-06-12 06:57:43",77,"YT4MFDX648SJXCISGAZM"
999989,"2018-06-12 06:57:44",24,"O3Q4ZW9CFLQYMKSEB29R"
999990,"2018-06-12 06:57:45",84,"ZVSC7SG2JR5YI2T0VRSI"
999991,"2018-06-12 06:57:46",94,"2UDBCNL284YI10AIK2F0"
999992,"2018-06-12 06:57:47",50,"D3TRNFJ7U0CM7A7FX90L"
999993,"2018-06-12 06:57:48",4,"1T691NVGV96MRTITUJHL"
999994,"2018-06-12 06:57:49",40,"UC9PHXKJ54HTOM4GN4BT"
999995,"2018-06-12 06:57:50",36,"GS6505QXJOVXBRA3TKS0"
999996,"2018-06-12 06:57:51",95,"PYSR8HM4OWYDKZDFGEG8"
999997,"2018-06-12 06:57:52",40,"3F84RGP9O21R680NR5TN"
999998,"2018-06-12 06:57:53",86,"X8VAQ7OK1ZVG7TQ9GH5P"
999999,"2018-06-12 06:57:54",15,"F26E8RI839DH9D6GSCHO"
1000000,"2018-06-12 06:57:55",15,"49UHFOHTGB9Q1BHMTZAY"

30 rows selected. 

Elapsed: 00:00:01.388

(2) HTML – query data has shown the format with “html” file (due to the content of html source code is long too much, thus I use SPOOL command to export SQL query result to a html file “test_999970_1000000.html”, the following is my SQL code for SPOOL;

[sqlcl@test 2018-06-13]$ pwd
/home/sqlcl/dsj_test/2018-06-13
[sqlcl@test 2018-06-13]$
[sqlcl@test 2018-06-13]$ cat spool_test_999970_1000000.sql
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

select sysdate as start_date from dual;

set sqlformat html
set echo off
set feedback off
set newpage none
set verify off
set define off
set pagesize 0
set linesize 4000
set termout off
set trimspool on
set heading on
set timing off

spool "/home/sqlcl/dsj_test/2018-06-13/test_999970_1000000.html"
select * from test where rownum <= 1000000
minus
select * from test where rownum <= 999970
;
spool off

set sqlformat
set echo on
set feedback on
set verify on
set define on
set pagesize 14
set linesize 80
set termout on

select sysdate as end_date from dual;

Next I run the SQL code “spool_test_999970_1000000.sql” to generate a html file “test_999970_1000000.html”, it’s my execution result as follows,

@/home/sqlcl/dsj_test/2018-06-13/spool_test_999970_1000000.sql

Session altered.

START_DATE
-------------------
2018-06-13 17:06:37

select sysdate as end_date from dual;
END_DATE
-------------------
2018-06-13 17:06:42

1 row selected.

BTW currently WordPress.com doesn’t support html file uploads to Media Documents Library through my Admin account management. Hence I only present 2 screenshots of “test_999970_1000000.html” the following,

1

2

(3) XML – query data has shown the format with “xml”, the XML format produces a tag-based XML document. All data is presented as CDATA tags. Due to the content of outputting xml is very long, thus I omit the rest of ROWs and only reserve 5 (the first three and the last two) number of ROWs. The following 2 screenshots are what you can see.

8

9

(4) JSON – query data has shown the format with “json”, the JSON format produces a JSON document containing the definitions of the columns along with the data they contain.

set sqlformat json

select * from test where rownum <= 1000000
minus
select * from test where rownum <= 999970
;
{"results":[{"columns":[{"name":"ID","type":"NUMBER"},{"name":"INC_DATETIME","type":"VARCHAR2"},{"name":"RANDOM_ID","type":"NUMBER"},{"name":"RANDOM_STRING","type":"VARCHAR2"}],"items":
[
{"id":999971,"inc_datetime":"2018-06-12 06:57:26","random_id":99,"random_string":"0KW71P3SRUQRMT44HLN0"}
,{"id":999972,"inc_datetime":"2018-06-12 06:57:27","random_id":32,"random_string":"KOWDZ7GMTKU3ENC3V71Z"}
,{"id":999973,"inc_datetime":"2018-06-12 06:57:28","random_id":93,"random_string":"FMSUGGDME5USWEK4NOIO"}
,{"id":999974,"inc_datetime":"2018-06-12 06:57:29","random_id":63,"random_string":"V9D4POC5HJ3N4F5DQLLL"}
,{"id":999975,"inc_datetime":"2018-06-12 06:57:30","random_id":49,"random_string":"MG8OIA0LJUW9BJCH7LXW"}
,{"id":999976,"inc_datetime":"2018-06-12 06:57:31","random_id":69,"random_string":"19AY956P9545VKSO4A77"}
,{"id":999977,"inc_datetime":"2018-06-12 06:57:32","random_id":5,"random_string":"QUMZLMRBNANVRKQDU8J6"}
,{"id":999978,"inc_datetime":"2018-06-12 06:57:33","random_id":72,"random_string":"OQG1ZONOEOORB1VWPKSH"}
,{"id":999979,"inc_datetime":"2018-06-12 06:57:34","random_id":36,"random_string":"4XOV8F08B0WXIRUAMA1F"}
,{"id":999980,"inc_datetime":"2018-06-12 06:57:35","random_id":98,"random_string":"EMPRO9B6CLO4EKYW270N"}
,{"id":999981,"inc_datetime":"2018-06-12 06:57:36","random_id":58,"random_string":"B7GLMO6X4K742VMY7ERM"}
,{"id":999982,"inc_datetime":"2018-06-12 06:57:37","random_id":91,"random_string":"GFGWFT2AAVDMFZ9DXMEV"}
,{"id":999983,"inc_datetime":"2018-06-12 06:57:38","random_id":36,"random_string":"GNKQDRDNO4G318MANXZ1"}
,{"id":999984,"inc_datetime":"2018-06-12 06:57:39","random_id":30,"random_string":"Q07DGT8C2S7AH6T7A2I7"}
,{"id":999985,"inc_datetime":"2018-06-12 06:57:40","random_id":75,"random_string":"EWL8QN6DZX5VKT3EYQU6"}
,{"id":999986,"inc_datetime":"2018-06-12 06:57:41","random_id":87,"random_string":"YMNX8IOSUOEYWZQUJTS5"}
,{"id":999987,"inc_datetime":"2018-06-12 06:57:42","random_id":50,"random_string":"YC1DV5J1GYGTEB53WIHV"}
,{"id":999988,"inc_datetime":"2018-06-12 06:57:43","random_id":77,"random_string":"YT4MFDX648SJXCISGAZM"}
,{"id":999989,"inc_datetime":"2018-06-12 06:57:44","random_id":24,"random_string":"O3Q4ZW9CFLQYMKSEB29R"}
,{"id":999990,"inc_datetime":"2018-06-12 06:57:45","random_id":84,"random_string":"ZVSC7SG2JR5YI2T0VRSI"}
,{"id":999991,"inc_datetime":"2018-06-12 06:57:46","random_id":94,"random_string":"2UDBCNL284YI10AIK2F0"}
,{"id":999992,"inc_datetime":"2018-06-12 06:57:47","random_id":50,"random_string":"D3TRNFJ7U0CM7A7FX90L"}
,{"id":999993,"inc_datetime":"2018-06-12 06:57:48","random_id":4,"random_string":"1T691NVGV96MRTITUJHL"}
,{"id":999994,"inc_datetime":"2018-06-12 06:57:49","random_id":40,"random_string":"UC9PHXKJ54HTOM4GN4BT"}
,{"id":999995,"inc_datetime":"2018-06-12 06:57:50","random_id":36,"random_string":"GS6505QXJOVXBRA3TKS0"}
,{"id":999996,"inc_datetime":"2018-06-12 06:57:51","random_id":95,"random_string":"PYSR8HM4OWYDKZDFGEG8"}
,{"id":999997,"inc_datetime":"2018-06-12 06:57:52","random_id":40,"random_string":"3F84RGP9O21R680NR5TN"}
,{"id":999998,"inc_datetime":"2018-06-12 06:57:53","random_id":86,"random_string":"X8VAQ7OK1ZVG7TQ9GH5P"}
,{"id":999999,"inc_datetime":"2018-06-12 06:57:54","random_id":15,"random_string":"F26E8RI839DH9D6GSCHO"}
,{"id":1000000,"inc_datetime":"2018-06-12 06:57:55","random_id":15,"random_string":"49UHFOHTGB9Q1BHMTZAY"}
]}]}
30 rows selected.

Elapsed: 00:00:01.326

(5) ANSICONSOLE – query data has shown the format with “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.

set sqlformat ansiconsole

set pagesize 50

select * from test where rownum <= 1000000
minus
select * from test where rownum <= 999970
;
ID INC_DATETIME RANDOM_ID RANDOM_STRING
999971 2018-06-12 06:57:26 99 0KW71P3SRUQRMT44HLN0
999972 2018-06-12 06:57:27 32 KOWDZ7GMTKU3ENC3V71Z
999973 2018-06-12 06:57:28 93 FMSUGGDME5USWEK4NOIO
999974 2018-06-12 06:57:29 63 V9D4POC5HJ3N4F5DQLLL
999975 2018-06-12 06:57:30 49 MG8OIA0LJUW9BJCH7LXW
999976 2018-06-12 06:57:31 69 19AY956P9545VKSO4A77
999977 2018-06-12 06:57:32 5 QUMZLMRBNANVRKQDU8J6
999978 2018-06-12 06:57:33 72 OQG1ZONOEOORB1VWPKSH
999979 2018-06-12 06:57:34 36 4XOV8F08B0WXIRUAMA1F
999980 2018-06-12 06:57:35 98 EMPRO9B6CLO4EKYW270N
999981 2018-06-12 06:57:36 58 B7GLMO6X4K742VMY7ERM
999982 2018-06-12 06:57:37 91 GFGWFT2AAVDMFZ9DXMEV
999983 2018-06-12 06:57:38 36 GNKQDRDNO4G318MANXZ1
999984 2018-06-12 06:57:39 30 Q07DGT8C2S7AH6T7A2I7
999985 2018-06-12 06:57:40 75 EWL8QN6DZX5VKT3EYQU6
999986 2018-06-12 06:57:41 87 YMNX8IOSUOEYWZQUJTS5
999987 2018-06-12 06:57:42 50 YC1DV5J1GYGTEB53WIHV
999988 2018-06-12 06:57:43 77 YT4MFDX648SJXCISGAZM
999989 2018-06-12 06:57:44 24 O3Q4ZW9CFLQYMKSEB29R
999990 2018-06-12 06:57:45 84 ZVSC7SG2JR5YI2T0VRSI
999991 2018-06-12 06:57:46 94 2UDBCNL284YI10AIK2F0
999992 2018-06-12 06:57:47 50 D3TRNFJ7U0CM7A7FX90L
999993 2018-06-12 06:57:48 4 1T691NVGV96MRTITUJHL
999994 2018-06-12 06:57:49 40 UC9PHXKJ54HTOM4GN4BT
999995 2018-06-12 06:57:50 36 GS6505QXJOVXBRA3TKS0
999996 2018-06-12 06:57:51 95 PYSR8HM4OWYDKZDFGEG8
999997 2018-06-12 06:57:52 40 3F84RGP9O21R680NR5TN
999998 2018-06-12 06:57:53 86 X8VAQ7OK1ZVG7TQ9GH5P
999999 2018-06-12 06:57:54 15 F26E8RI839DH9D6GSCHO
1000000 2018-06-12 06:57:55 15 49UHFOHTGB9Q1BHMTZAY

30 rows selected.

Elapsed: 00:00:01.389

Due to the display effect of this format with ANSICONSOLE for Posting Source Code via WordPress about column heading has a little different from real running result on the SQLcl, so I attach 2 pictures the following and it can compare very well to  my SQL code above.

3

4

(6) INSERT – query data has shown the format with “insert”, the INSERT format produces the INSERT statements that could be used to recreate the rows in a table.

set sqlformat insert

set pagesize 50

select * from test where rownum <= 1000000
minus
select * from test where rownum <= 999970
;
REM INSERTING into TEST
SET DEFINE OFF;
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999971,'2018-06-12 06:57:26',99,'0KW71P3SRUQRMT44HLN0');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999972,'2018-06-12 06:57:27',32,'KOWDZ7GMTKU3ENC3V71Z');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999973,'2018-06-12 06:57:28',93,'FMSUGGDME5USWEK4NOIO');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999974,'2018-06-12 06:57:29',63,'V9D4POC5HJ3N4F5DQLLL');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999975,'2018-06-12 06:57:30',49,'MG8OIA0LJUW9BJCH7LXW');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999976,'2018-06-12 06:57:31',69,'19AY956P9545VKSO4A77');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999977,'2018-06-12 06:57:32',5,'QUMZLMRBNANVRKQDU8J6');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999978,'2018-06-12 06:57:33',72,'OQG1ZONOEOORB1VWPKSH');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999979,'2018-06-12 06:57:34',36,'4XOV8F08B0WXIRUAMA1F');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999980,'2018-06-12 06:57:35',98,'EMPRO9B6CLO4EKYW270N');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999981,'2018-06-12 06:57:36',58,'B7GLMO6X4K742VMY7ERM');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999982,'2018-06-12 06:57:37',91,'GFGWFT2AAVDMFZ9DXMEV');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999983,'2018-06-12 06:57:38',36,'GNKQDRDNO4G318MANXZ1');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999984,'2018-06-12 06:57:39',30,'Q07DGT8C2S7AH6T7A2I7');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999985,'2018-06-12 06:57:40',75,'EWL8QN6DZX5VKT3EYQU6');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999986,'2018-06-12 06:57:41',87,'YMNX8IOSUOEYWZQUJTS5');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999987,'2018-06-12 06:57:42',50,'YC1DV5J1GYGTEB53WIHV');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999988,'2018-06-12 06:57:43',77,'YT4MFDX648SJXCISGAZM');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999989,'2018-06-12 06:57:44',24,'O3Q4ZW9CFLQYMKSEB29R');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999990,'2018-06-12 06:57:45',84,'ZVSC7SG2JR5YI2T0VRSI');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999991,'2018-06-12 06:57:46',94,'2UDBCNL284YI10AIK2F0');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999992,'2018-06-12 06:57:47',50,'D3TRNFJ7U0CM7A7FX90L');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999993,'2018-06-12 06:57:48',4,'1T691NVGV96MRTITUJHL');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999994,'2018-06-12 06:57:49',40,'UC9PHXKJ54HTOM4GN4BT');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999995,'2018-06-12 06:57:50',36,'GS6505QXJOVXBRA3TKS0');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999996,'2018-06-12 06:57:51',95,'PYSR8HM4OWYDKZDFGEG8');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999997,'2018-06-12 06:57:52',40,'3F84RGP9O21R680NR5TN');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999998,'2018-06-12 06:57:53',86,'X8VAQ7OK1ZVG7TQ9GH5P');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (999999,'2018-06-12 06:57:54',15,'F26E8RI839DH9D6GSCHO');
Insert into TEST (ID,INC_DATETIME,RANDOM_ID,RANDOM_STRING) values (1000000,'2018-06-12 06:57:55',15,'49UHFOHTGB9Q1BHMTZAY');

30 rows selected.

Elapsed: 00:00:01.365

(7) LOADER – query data has shown the format with “loader”, the LOADER format produces pipe delimited output with string values enclose by double-quotes. The column names are not included in the output.

set sqlformat loader

select * from test where rownum <= 1000000
minus
select * from test where rownum <= 999970
;
999971|"2018-06-12 06:57:26"|99|"0KW71P3SRUQRMT44HLN0"|
999972|"2018-06-12 06:57:27"|32|"KOWDZ7GMTKU3ENC3V71Z"|
999973|"2018-06-12 06:57:28"|93|"FMSUGGDME5USWEK4NOIO"|
999974|"2018-06-12 06:57:29"|63|"V9D4POC5HJ3N4F5DQLLL"|
999975|"2018-06-12 06:57:30"|49|"MG8OIA0LJUW9BJCH7LXW"|
999976|"2018-06-12 06:57:31"|69|"19AY956P9545VKSO4A77"|
999977|"2018-06-12 06:57:32"|5|"QUMZLMRBNANVRKQDU8J6"|
999978|"2018-06-12 06:57:33"|72|"OQG1ZONOEOORB1VWPKSH"|
999979|"2018-06-12 06:57:34"|36|"4XOV8F08B0WXIRUAMA1F"|
999980|"2018-06-12 06:57:35"|98|"EMPRO9B6CLO4EKYW270N"|
999981|"2018-06-12 06:57:36"|58|"B7GLMO6X4K742VMY7ERM"|
999982|"2018-06-12 06:57:37"|91|"GFGWFT2AAVDMFZ9DXMEV"|
999983|"2018-06-12 06:57:38"|36|"GNKQDRDNO4G318MANXZ1"|
999984|"2018-06-12 06:57:39"|30|"Q07DGT8C2S7AH6T7A2I7"|
999985|"2018-06-12 06:57:40"|75|"EWL8QN6DZX5VKT3EYQU6"|
999986|"2018-06-12 06:57:41"|87|"YMNX8IOSUOEYWZQUJTS5"|
999987|"2018-06-12 06:57:42"|50|"YC1DV5J1GYGTEB53WIHV"|
999988|"2018-06-12 06:57:43"|77|"YT4MFDX648SJXCISGAZM"|
999989|"2018-06-12 06:57:44"|24|"O3Q4ZW9CFLQYMKSEB29R"|
999990|"2018-06-12 06:57:45"|84|"ZVSC7SG2JR5YI2T0VRSI"|
999991|"2018-06-12 06:57:46"|94|"2UDBCNL284YI10AIK2F0"|
999992|"2018-06-12 06:57:47"|50|"D3TRNFJ7U0CM7A7FX90L"|
999993|"2018-06-12 06:57:48"|4|"1T691NVGV96MRTITUJHL"|
999994|"2018-06-12 06:57:49"|40|"UC9PHXKJ54HTOM4GN4BT"|
999995|"2018-06-12 06:57:50"|36|"GS6505QXJOVXBRA3TKS0"|
999996|"2018-06-12 06:57:51"|95|"PYSR8HM4OWYDKZDFGEG8"|
999997|"2018-06-12 06:57:52"|40|"3F84RGP9O21R680NR5TN"|
999998|"2018-06-12 06:57:53"|86|"X8VAQ7OK1ZVG7TQ9GH5P"|
999999|"2018-06-12 06:57:54"|15|"F26E8RI839DH9D6GSCHO"|
1000000|"2018-06-12 06:57:55"|15|"49UHFOHTGB9Q1BHMTZAY"|

30 rows selected.

Elapsed: 00:00:01.297

(8) FIXED – query data has shown the format with “fixed”, that’s to say, fixed column width for outputting each column, all data enclose by double-quotes. The first line contains the column names.

set sqlformat fixed

select * from test where rownum <= 1000000
minus
select * from test where rownum <= 999970
;
"ID" "INC_DATETIME" "RANDOM_ID" "RANDOM_STRING"
"999971" "2018-06-12 06:57:26" "99" "0KW71P3SRUQRMT44HLN0"
"999972" "2018-06-12 06:57:27" "32" "KOWDZ7GMTKU3ENC3V71Z"
"999973" "2018-06-12 06:57:28" "93" "FMSUGGDME5USWEK4NOIO"
"999974" "2018-06-12 06:57:29" "63" "V9D4POC5HJ3N4F5DQLLL"
"999975" "2018-06-12 06:57:30" "49" "MG8OIA0LJUW9BJCH7LXW"
"999976" "2018-06-12 06:57:31" "69" "19AY956P9545VKSO4A77"
"999977" "2018-06-12 06:57:32" "5" "QUMZLMRBNANVRKQDU8J6"
"999978" "2018-06-12 06:57:33" "72" "OQG1ZONOEOORB1VWPKSH"
"999979" "2018-06-12 06:57:34" "36" "4XOV8F08B0WXIRUAMA1F"
"999980" "2018-06-12 06:57:35" "98" "EMPRO9B6CLO4EKYW270N"
"999981" "2018-06-12 06:57:36" "58" "B7GLMO6X4K742VMY7ERM"
"999982" "2018-06-12 06:57:37" "91" "GFGWFT2AAVDMFZ9DXMEV"
"999983" "2018-06-12 06:57:38" "36" "GNKQDRDNO4G318MANXZ1"
"999984" "2018-06-12 06:57:39" "30" "Q07DGT8C2S7AH6T7A2I7"
"999985" "2018-06-12 06:57:40" "75" "EWL8QN6DZX5VKT3EYQU6"
"999986" "2018-06-12 06:57:41" "87" "YMNX8IOSUOEYWZQUJTS5"
"999987" "2018-06-12 06:57:42" "50" "YC1DV5J1GYGTEB53WIHV"
"999988" "2018-06-12 06:57:43" "77" "YT4MFDX648SJXCISGAZM"
"999989" "2018-06-12 06:57:44" "24" "O3Q4ZW9CFLQYMKSEB29R"
"999990" "2018-06-12 06:57:45" "84" "ZVSC7SG2JR5YI2T0VRSI"
"999991" "2018-06-12 06:57:46" "94" "2UDBCNL284YI10AIK2F0"
"999992" "2018-06-12 06:57:47" "50" "D3TRNFJ7U0CM7A7FX90L"
"999993" "2018-06-12 06:57:48" "4" "1T691NVGV96MRTITUJHL"
"999994" "2018-06-12 06:57:49" "40" "UC9PHXKJ54HTOM4GN4BT"
"999995" "2018-06-12 06:57:50" "36" "GS6505QXJOVXBRA3TKS0"
"999996" "2018-06-12 06:57:51" "95" "PYSR8HM4OWYDKZDFGEG8"
"999997" "2018-06-12 06:57:52" "40" "3F84RGP9O21R680NR5TN"
"999998" "2018-06-12 06:57:53" "86" "X8VAQ7OK1ZVG7TQ9GH5P"
"999999" "2018-06-12 06:57:54" "15" "F26E8RI839DH9D6GSCHO"
"1000000" "2018-06-12 06:57:55" "15" "49UHFOHTGB9Q1BHMTZAY"

30 rows selected.

Elapsed: 00:00:01.364

Being the same reason as (5) ANSICONSOLE, I add the following 3 pictures for comparing very well to the previous SQL code.

5

6

7

(9) DEFAULT – query data has shown the format with “default”, the DEFAULT option clears all previous SQLFORMAT settings and returns to the default output.

desc test
Name          Null? Type
------------- ----- --------------
ID                  NUMBER
INC_DATETIME        VARCHAR2(19)
RANDOM_ID           NUMBER
RANDOM_STRING       VARCHAR2(4000)
set sqlformat default
SQL Format Cleared

set linesize 200
set pagesize 50

col random_string for a20

select * from test where rownum <= 1000000
minus
select * from test where rownum <= 999970
;
ID     INC_DATETIME        RANDOM_ID  RANDOM_STRING
------ ------------------- ---------- --------------------
999971 2018-06-12 06:57:26         99 0KW71P3SRUQRMT44HLN0
999972 2018-06-12 06:57:27         32 KOWDZ7GMTKU3ENC3V71Z
999973 2018-06-12 06:57:28         93 FMSUGGDME5USWEK4NOIO
999974 2018-06-12 06:57:29         63 V9D4POC5HJ3N4F5DQLLL
999975 2018-06-12 06:57:30         49 MG8OIA0LJUW9BJCH7LXW
999976 2018-06-12 06:57:31         69 19AY956P9545VKSO4A77
999977 2018-06-12 06:57:32          5 QUMZLMRBNANVRKQDU8J6
999978 2018-06-12 06:57:33         72 OQG1ZONOEOORB1VWPKSH
999979 2018-06-12 06:57:34         36 4XOV8F08B0WXIRUAMA1F
999980 2018-06-12 06:57:35         98 EMPRO9B6CLO4EKYW270N
999981 2018-06-12 06:57:36         58 B7GLMO6X4K742VMY7ERM
999982 2018-06-12 06:57:37         91 GFGWFT2AAVDMFZ9DXMEV
999983 2018-06-12 06:57:38         36 GNKQDRDNO4G318MANXZ1
999984 2018-06-12 06:57:39         30 Q07DGT8C2S7AH6T7A2I7
999985 2018-06-12 06:57:40         75 EWL8QN6DZX5VKT3EYQU6
999986 2018-06-12 06:57:41         87 YMNX8IOSUOEYWZQUJTS5
999987 2018-06-12 06:57:42         50 YC1DV5J1GYGTEB53WIHV
999988 2018-06-12 06:57:43         77 YT4MFDX648SJXCISGAZM
999989 2018-06-12 06:57:44         24 O3Q4ZW9CFLQYMKSEB29R
999990 2018-06-12 06:57:45         84 ZVSC7SG2JR5YI2T0VRSI
999991 2018-06-12 06:57:46         94 2UDBCNL284YI10AIK2F0
999992 2018-06-12 06:57:47         50 D3TRNFJ7U0CM7A7FX90L
999993 2018-06-12 06:57:48          4 1T691NVGV96MRTITUJHL
999994 2018-06-12 06:57:49         40 UC9PHXKJ54HTOM4GN4BT
999995 2018-06-12 06:57:50         36 GS6505QXJOVXBRA3TKS0
999996 2018-06-12 06:57:51         95 PYSR8HM4OWYDKZDFGEG8
999997 2018-06-12 06:57:52         40 3F84RGP9O21R680NR5TN
999998 2018-06-12 06:57:53         86 X8VAQ7OK1ZVG7TQ9GH5P
999999 2018-06-12 06:57:54         15 F26E8RI839DH9D6GSCHO
1000000 2018-06-12 06:57:55        15 49UHFOHTGB9Q1BHMTZAY

30 rows selected.

Elapsed: 00:00:01.301

In summary, it's all of the introduction about “SET SQLFORMAT” on SQLcl 18.1.1, many thank you for giving your valuable time to read these information.

I hope this help for you. ♥ Good luck ♥

 


 

Related Reference

 

Update

Jun 14, 2018 : according to Jonathan gave me a comment on his blog post “dbms_random”, I erased all this prompt “SQL> “ in all of my SQL code snippet because the appearance of the “less than / greater than” symbols (“<“ need to be written “&lt;” or “>” need to be written “&gt;”) often result in code getting lost on WordPress;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s