It’s a SQLcl / SQL Developer scripting feature. If you run this in a SQL Worksheet using F5 (Script)
help set sqlformat
You’ll get back a pretty decent description of the feature.
SET SQLFORMAT
SET SQLFORMAT { default,csv,html,xml,json,fixed,insert,loader,delimited,ansiconsole}
default : SQL*PLUS style formatting
csv : comma separated and string enclosed with "
html : html tabular format
xml : xml format of /results/rows/column/*
json : json format matching ORDS Collection Format
json-formatted : json format matching ORDS Collection Format and pretty printed
fixed : fixed width
insert : generates insert statements from sql results
Example
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH.MI.SSXFF AM'),800,null,20);
delimited : CSV format with optional separator , left, and right enclosure
set sqlformat delimited [separator] [left enclosure] [right enclosure]
Example:
set sqlformat delimited , < >
7369,,,7902,17-DEC-80,800,,20,5555555555554444
ansiconsole : advanced formatting based on data and terminal size
set sqlformat ansiconsole : base format
set sqlformat ansiconsole default : number formatting to ###,###.###
set sqlformat ansiconsole : Mask following Java DecimalFormat
4 Comments
Thanks Jeff
inside this complex web of functions and features it is not easy t find where they come from .. 😉
Any SET command will have built-in help, if that fails, Google will almost always lead you to the right spot.
SQL DEV 21.4. The search in the online-help for SQLFORMAT gives no results – is it indented?
It’s a SQLcl / SQL Developer scripting feature. If you run this in a SQL Worksheet using F5 (Script)
help set sqlformat
You’ll get back a pretty decent description of the feature.
SET SQLFORMAT
SET SQLFORMAT { default,csv,html,xml,json,fixed,insert,loader,delimited,ansiconsole}
default : SQL*PLUS style formatting
csv : comma separated and string enclosed with "
html : html tabular format
xml : xml format of /results/rows/column/*
json : json format matching ORDS Collection Format
json-formatted : json format matching ORDS Collection Format and pretty printed
fixed : fixed width
insert : generates insert statements from sql results
Example
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH.MI.SSXFF AM'),800,null,20);
loader : pipe (|) delimited enclosed with "
Example:
7369|"SMITH"|"CLERK"|7902|"1980-12-17 00:00:00"|800||20|5555555555554444|
delimited : CSV format with optional separator , left, and right enclosure,,7902,17-DEC-80,800,,20,5555555555554444
set sqlformat delimited [separator] [left enclosure] [right enclosure]
Example:
set sqlformat delimited , < >
7369,
ansiconsole : advanced formatting based on data and terminal size : Mask following Java DecimalFormat
set sqlformat ansiconsole : base format
set sqlformat ansiconsole default : number formatting to ###,###.###
set sqlformat ansiconsole
https://docs.oracle.com/javase/8/docs/api/java/text/DecimalFormat.html
set sqlformat ansiconsole -config=highlight.json : highlight matches in results
highlight options :
Example :
{"highlights":[
{"type":"startWith","test":"W","color":"INTENSITY_BOLD,CYAN"},
{"type":"endWith","test":"MAN","color":"BLUE"},
{"type":"contains","test":"MIT","color":"YELLOW"},
{"type":"exact","test":"FORD","color":"GREEN"},
{"type":"regex","test":"[0-9]{2}","color":"MAGENTA"}
]
}