Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

SQLcl: ANSICONSOLE and Column Header Formatting

1 Min Read

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.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

3 Comments

  1. Sean Reply

    When adding color codes to query results for ansiconsole the size of the string is inflated beyond what will be displayed.
    The result string with @|bg_red…….|@ and other control strings require wide column types to hold them, which the autosizing then stretches the column width so far it causes line wrapping in the output for text that is actually not that big.

    Is there a way to tell the autosizing to only resize according to the width of what will be displayed and not the actual text underneath?

  2. Dave Venus Reply

    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

Write A Comment