SQLcl: MCP Server & SQL Prompt

Tested using SQLcl 23.3, on Windows 11

This is just for the record.

Start by running the following script file in SQLcl:

set sqlformat ansiconsole
show sqlformat

clear columns
column str1 format a10 word_wrapped
column str2 format a10 word_wrapped

column

--set heading off

with t(str1, str2) as (
    select '-0-1-2-3-4-5-6-7-8-9', 'abcdefghijklmnopqrstuvwxyz' from dual
)
select * from t;

--set heading on

Readout:

SQL Format : ansiconsole
COLUMN  str1 ON
FORMAT  a10
word_wrap
COLUMN  str2 ON
FORMAT  a10
word_wrap

STR1                    STR2
_______________________ _____________________________
-0-1-2-3-4-5-6-7-8-9    abcdefghijklmnopqrstuvwxyz

1 row selected.

So far, so good. Because SQLFORMAT is set to ANSICONSOLE, the format attributes in the 2 column specifications have been ignored, right as expected.

(Perhaps not so expected is that a NOPRINT attribute would be ignored just the same—verifying that, e.g. on column STR1 is left as an exercise to the reader.)

Then remove the comments on the SET HEADING command lines in the above script, and run it again.

Readout:

SQL Format : ansiconsole
COLUMN  str1 ON
FORMAT  a10
word_wrap
COLUMN  str2 ON
FORMAT  a10
word_wrap

-0-1-2-3-4 abcdefghij
-5-6-7-8-9 klmnopqrst
           uvwxyz

1 row selected.

This makes it perfectly clear that, when HEADING is OFF, the SQLFORMAT set to ANSICONSOLE is not honored, and the DEFAULT, SQL*Plus-like, formatting mode is used instead.

Bottom line: don't expect to be able to use HEADING OFF along with ANSICONSOLE formatting, at least not in SQLcl 23.3.

Regards,