SQLcl 19.1 sqlformat options

The biggest change for was that the help was overhauled to include description and examples. While some format like csv are very straight forward on what to expect others like loader or delimited are not. Hopefully this make things like delimited a lot more clear on how to set the left , right , and separator characters.

The only new format is now there’s a json-formatted which is meant to be more readable format by doing a pretty print to the screen.

SQL> help set sqlformat
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,<SMITH>,<CLERK>,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 <number format mask>  : 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":"endsWith","test":"MAN","color":"BLUE"},
                        {"type":"contains","test":"MIT","color":"YELLOW"},
                        {"type":"exact","test":"FORD","color":"GREEN"},
                        {"type":"regex","test":"[0-9]{2}","color":"MAGENTA"}
                      ]
                    } 

   
SQL> 

json-formatted

The new json-formatted is purty.

SQL> set sqlformat json-formatted
SQL> select * from emp;
{
  "results" : [
    {
      "columns" : [
        {
          "name" : "EMPNO",
          "type" : "NUMBER"
        },
        {
          "name" : "ENAME",
          "type" : "VARCHAR2"
        },
        {
....

ansiconsole

I spent some time getting the headers and spacing more aligned so the ansiconsole format will be aligned as expected there were a number of cases where this wasn’t happening.

The larger change here is to add the highlighting feature. The help has a full example of the options that are useful as a test against a select * from emp table. Yes theres is a bug here that is the example has endWith and should be endsWith

The types in this are a direct usage of the underlying java.lang.String methods with in some cases the same names

exact - This is not a function rather a simple equality check and is case sensative.

startsWith - Tests if this string starts with the specified prefix.

endsWith - Tests if this string ends with the specified suffix.

contains - Returns true if and only if this string contains the specified sequence of char values.

regex - Tells whether or not this string matches the given regular expression.

One thing to note is the config file is re-read with every execution to make iterating the setting easier vs caching it at the time of the sqlformat command being issued.