sqlformat

4 Comments

1. 

   Peter2 2 years ago Reply

   Thanks Jeff
   inside this complex web of functions and features it is not easy t find where they come from .. 😉

   • 

     thatjeffsmith Post Author 2 years ago

     Any SET command will have built-in help, if that fails, Google will almost always lead you to the right spot.

2. 

   Peter2 2 years ago Reply

   SQL DEV 21.4. The search in the online-help for SQLFORMAT gives no results – is it indented?

   • 

     thatjeffsmith Post Author 2 years ago

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

     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"}
]
}