SQLcl: ANSICONSOLE and Column Header Formatting

thatjeffsmith SQL Developer 2 Comments

Tell Others About This Story:

Question: Why is SQLcl truncating my column header names? It doesn’t do it if I use SET SQLFORMAT ANSICONSOLE though…

Answer: Because, SQL*Plus.

Longer Answer: SQLcl follows the SQL*Plus rules.

And in this case, the rule is:

The default width of CHAR and VARCHAR2 (VARCHAR) columns is the width of the column in the database.

The questioner was seeing that the 2nd column in their query was having its column name truncated – because it was a VARCHAR2(3) defined column with a name having many more than 3 characters.


Tip: When in doubt, try your code in SQL*Plus too. If you see the same ‘weird’ behavior there as well, maybe the problem is your code, or misunderstanding of our CLI formatting rules and procedures.

SQL> CREATE TABLE dave
  2  (sak_aid_elig NUMBER,
  3   cde_aid_elig_reason varchar2(3),
  4   dte_added NUMBER);
 
TABLE DAVE created.
 
SQL> INSERT INTO dave VALUES (
  2   674008026, '052', 21121218);
 
1 ROW inserted.
 
SQL> INSERT INTO dave VALUES (
  2   674008026, '404', 21160706);
 
1 ROW inserted.
 
SQL> commit;
 
Commit complete.
 
SQL> SET sqlformat
SQL Format Cleared
SQL> SELECT * FROM dave;
 
SAK_AID_ELIG CDE  DTE_ADDED
------------ --- ----------
   674008026 052   21121218
   674008026 404   21160706
 
 
SQL> SET sqlformat ansiconsole
SQL> /
 
SAK_AID_ELIG  CDE_AID_ELIG_REASON  DTE_ADDED
674008026     052                  21121218
674008026     404                  21160706
 
 
SQL> col cde_aid_elig_reason format a20
SQL> SET sqlformat
SQL Format Cleared
SQL> /
 
SAK_AID_ELIG CDE_AID_ELIG_REASON   DTE_ADDED
------------ -------------------- ----------
   674008026 052                    21121218
   674008026 404                    21160706
 
SQL>

The important bit there is this:
SQL> col cde_aid_elig_reason format a20

That’s a SQL*Plus thing – otherwise the definition of the column shapes the size of the column header.

SET SQLFORMAT ANSICONSOLE – in addition to smart-sizing the data itself, it also prints the full column name, no matter what. Your SQL*Plus report style formatting commands are pretty much ignored when using a SQLFORMAT – so pick one poison or the other.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 2

  1. Hi!

    This is a wishlist request for SQLcl 🙂

    The ability to run SQLDeveloper reports both as a command line option and from the SQLcl prompt. Not create (though you probably could do that thru javascript!) just run and generate the output. We are more likely to have SQLcl on our servers than SQLDeveloper.

    Thanks,

    Dave

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *