SQLcl: SET SQLFORMAT

SET SQLFORMAT { default,csv,html,xml,json,fixed,insert,loader,delimited,ansiconsole}

SQL> select * from (values(1, 'one', 'foo'),(2, 'two', 'baz'),(3,'three', 'bar')) data(id, num, txt);

   ID NUM      TXT
_____ ________ ______
    1 one      foo
    2 two      baz
    3 three    bar

SQL> set sqlformat csv
SQL> /
"ID","NUM","TXT"
1,"one","foo"
2,"two","baz"
3,"three","bar"

SQL> set sqlformat xml
/
<?xml version='1.0'  encoding='UTF-8' ?>
<RESULTS>
        <ROW>
                <COLUMN NAME="ID"><![CDATA[1]]></COLUMN>
                <COLUMN NAME="NUM"><![CDATA[one]]></COLUMN>
                <COLUMN NAME="TXT"><![CDATA[foo]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="ID"><![CDATA[2]]></COLUMN>
                <COLUMN NAME="NUM"><![CDATA[two]]></COLUMN>
                <COLUMN NAME="TXT"><![CDATA[baz]]></COLUMN>
        </ROW>
        <ROW>
                <COLUMN NAME="ID"><![CDATA[3]]></COLUMN>
                <COLUMN NAME="NUM"><![CDATA[three]]></COLUMN>
                <COLUMN NAME="TXT"><![CDATA[bar]]></COLUMN>
        </ROW>
</RESULTS>

SQL> set sqlformat json
/
{"results":[{"columns":[{"name":"ID","type":"NUMBER"},{"name":"NUM","type":"VARCHAR2"},{"name":"TXT","type":"CHAR"}],"items":
[
{"id":1,"num":"one","txt":"foo"}
,{"id":2,"num":"two","txt":"baz"}
,{"id":3,"num":"three","txt":"bar"}
]}]}

set sqlformat ansiconsole -config…

{"highlights":[
  {"type":"startWith","test": "si"      ,"color":"INTENSITY_BOLD,CYAN"           },
  {"type":"endsWith" ,"test": "blink"   ,"color":"BLINK_SLOW,INTENSITY_BOLD,BLUE"},
  {"type":"contains" ,"test": "needle"  ,"color":"BG_WHITE,RED"                  },
  {"type":"exact"    ,"test": "ghi"     ,"color":"GREEN"                         },
  {"type":"regex"    ,"test": "[0-9]+"  ,"color":"INTENSITY_BOLD,YELLOW"         }
]}

Github repository about-SQLcl, path: /set/sqlformat/highlight.json

set sqlformat ansiconsole -config=highlight.json

select * from (values
  (  1, 'one'          , 'abc'         ),
  (800, 'eight hundred', '17 25'       ),
  ( 98, 'ninety-eight' , 'ghi'         ),
  ( 55, 'fifty-five'   , 'theblink'    ),
  ( 22, 'twenty-two'   , 'defneedleghi'),
  ( 61, 'sixty-one'    , 'noo moo doo' ),
  ( 73, 'seventy-three', 'w x y z'     ),
  (800, 'eight hundred', '17 25'       ),
  ( 99, 'ninety-nine'  , 'ghilmn'      )
)
tmp (id, num, txt);

Github repository about-SQLcl, path: /set/sqlformat/ansiconsole-config.sql

The idea is cool, but it would be great if the coloring could be based on the return value of a function that is invoked for each value.

It should be noted that the config/json file is reloaded with every SQL execution.

Kris Rice blog post on this feature was quite helpful to me.

The colors can apparently be chosen from the org.fusesource.jansi.AnsiRenderer.Code enum.

See also coloring the text in the prompt command.