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.
3 Comments
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?
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
You can use the sdcli to run the reports…