Infrastructure at your Service

Clemens Bleile

Automatic column formatting in Oracle sqlplus

By October 5, 2020 Oracle No Comments

Column formatting was always a pain in sqlplus when writing queries on the prompt. Most people use tools like SQL Developer or Quest TOAD which can scroll horizontally when running queries against a database, but as a consultant you are often still forced to use sqlplus. Here’s the issue: When running e.g. a query on a table T1 (which is a copy of ALL_OBJECTS) it looks by default as follows and is hard to read:

[email protected]@orcl> create table t1 as select * from all_objects;

Table created.

[email protected]@orcl> select owner, oracle_maintained, object_name from t1 where rownum < 4;

OWNER                                                                                                                            O
-------------------------------------------------------------------------------------------------------------------------------- -
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS                                                                                                                              Y
TS$

SYS                                                                                                                              Y
ICOL$

SYS                                                                                                                              Y
C_FILE#_BLOCK#

The column width is defined by the maximum length of the data type. I.e. for a VARCHAR2(128) a column of width 128 is defined in the output (if the linessize is less than the column width then the linesize defines the maximum column width displayed).

You can format columns of course:

[email protected]@orcl> column owner format a32
[email protected]@orcl> column object_name format a32
[email protected]@orcl> select owner, oracle_maintained, object_name from t1 where rownum < 4;

OWNER                            O OBJECT_NAME
-------------------------------- - --------------------------------
SYS                              Y TS$
SYS                              Y ICOL$
SYS                              Y C_FILE#_BLOCK#

But running lots of ad hoc queries in sqlplus is quite annoying if you have to format all columns manually.
This has been resolved in sqlcl by using “set sqlformat ansiconsole”:

[email protected]:/home/oracle/ [orcl] alias sqlcl="bash $ORACLE_HOME/sqldeveloper/sqldeveloper/bin/sql"
[email protected]:/home/oracle/ [orcl] sqlcl cbleile

SQLcl: Release 19.1 Production on Thu Oct 01 08:43:49 2020

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Password? (**********?) *******
Last Successful login time: Thu Oct 01 2020 08:43:51 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0


SQL> set sqlformat ansiconsole
SQL> select owner, oracle_maintained, object_name from t1 where rownum < 4;
OWNER   ORACLE_MAINTAINED   OBJECT_NAME      
------- ------------------- ----------------
SYS     Y                   TS$              
SYS     Y                   ICOL$            
SYS     Y                   C_FILE#_BLOCK#   

In sqlcl all rows up to “pagesize” are pre-fetched and the column-format is adjusted for the page to the maximum length per column. E.g.

SQL> set pagesize 1
SQL> select 'short' val from dual
  2  union all
  3  select 'looooooooooooooooooooooooooooooooooooooooooooooooooooooong' val from dual;
VAL     
-------
short   

VAL                                                          
------------------------------------------------------------
looooooooooooooooooooooooooooooooooooooooooooooooooooooong   

SQL> set pagesize 2
SQL> select 'short' val from dual
  2  union all
  3  select 'looooooooooooooooooooooooooooooooooooooooooooooooooooooong' val from dual;
VAL                                                          
------------------------------------------------------------
short   
looooooooooooooooooooooooooooooooooooooooooooooooooooooong   

REMARK: Due to the algorithm in sqlcl you can force sqlcl to crash with sqlformat ansiconsole if it does not have enough memory to pre-fetch the data for a single page. E.g. having lots of data returned and the maximum pagesize set (50000):

SQL> set sqlformat ansiconsole
SQL> set pagesize 50000
SQL> select 
  2  'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' a,
  3  'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' b,
  4  'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' c,
  5  'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' d,
....
315  'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' y3,
316  'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' z3
317  from all_objects;
Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
	at java.util.Arrays.copyOfRange(Arrays.java:3664)
	at java.lang.String.(String.java:207)
	at oracle.sql.CharacterSetUTF.toStringWithReplacement(CharacterSetUTF.java:134)
	at oracle.sql.CHAR.getStringWithReplacement(CHAR.java:307)
	at oracle.sql.CHAR.toString(CHAR.java:318)
	at oracle.sql.CHAR.stringValue(CHAR.java:411)
	at oracle.dbtools.raptor.nls.DefaultNLSProvider.format(DefaultNLSProvider.java:208)
	at oracle.dbtools.raptor.nls.OracleNLSProvider.format(OracleNLSProvider.java:214)
	at oracle.dbtools.raptor.utils.NLSUtils.format(NLSUtils.java:187)
	at oracle.dbtools.raptor.format.ANSIConsoleFormatter.printColumn(ANSIConsoleFormatter.java:149)
	at oracle.dbtools.raptor.format.ResultSetFormatterWrapper.print(ResultSetFormatterWrapper.java:274)
	at oracle.dbtools.raptor.format.ResultSetFormatterWrapper.print(ResultSetFormatterWrapper.java:222)
	at oracle.dbtools.raptor.format.ResultsFormatter.print(ResultsFormatter.java:518)
	at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:124)
	at oracle.dbtools.db.ResultSetFormatter.formatResults(ResultSetFormatter.java:70)
	at oracle.dbtools.raptor.newscriptrunner.SQL.processResultSet(SQL.java:798)
	at oracle.dbtools.raptor.newscriptrunner.SQL.executeQuery(SQL.java:709)
	at oracle.dbtools.raptor.newscriptrunner.SQL.run(SQL.java:83)
	at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.runSQL(ScriptRunner.java:404)
	at oracle.dbtools.raptor.newscriptrunner.ScriptRunner.run(ScriptRunner.java:230)
	at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:344)
	at oracle.dbtools.raptor.newscriptrunner.ScriptExecutor.run(ScriptExecutor.java:227)
	at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.process(SqlCli.java:404)
	at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.processLine(SqlCli.java:415)
	at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.startSQLPlus(SqlCli.java:1249)
	at oracle.dbtools.raptor.scriptrunner.cmdline.SqlCli.main(SqlCli.java:491)
[email protected]:/home/oracle/ [orcl] 

But back to sqlplus: To address the issue with the column-formatting several solutions were developed. E.g. Tom Kyte provided a procedure print_table over 20 years ago to list each column and its value in a separate line:

[email protected]@orcl> exec print_table('select owner, oracle_maintained, object_name from t1 where rownum < 4');
OWNER                 : SYS
ORACLE_MAINTAINED     : Y
OBJECT_NAME           : TS$
-----------------
OWNER                 : SYS
ORACLE_MAINTAINED     : Y
OBJECT_NAME           : ICOL$
-----------------
OWNER                 : SYS
ORACLE_MAINTAINED     : Y
OBJECT_NAME           : C_FILE#_BLOCK#
-----------------

PL/SQL procedure successfully completed.

The same can be done with xmltable since more recent versions. See e.g. here.

That was perfect when querying a couple of rows.

Alternatively some people used a terminal emulation which provided horizontal scrolling like terminator on Linux (see e.g. here).

What I wanted to provide in this blog is another solution. Usually the issue is with VARCHAR2-output. So I asked myself, why not formatting all VARCHAR2 columns of a table to their average length. I.e. I created a script colpp_table.sql (colpp, because my initial objective was to provide a column-width per page like in sqlcl) which takes the statistic avg_col_len from ALL_TAB_COLUMNS. To run the script I have to provide 2 parameters: The owner and table-name I use in my query later on:

[email protected]@orcl> !more colpp_table.sql
set termout off heading off lines 200 pages 999 trimspool on feed off timing off verify off
spool /tmp/&1._&2..sql
select 'column '||column_name||' format a'||to_char(decode(nvl(avg_col_len,data_length),0,1,nvl(avg_col_len,data_length))) 
from all_tab_columns 
where owner=upper('&1.') 
and table_name=upper('&2.') 
and data_type in ('VARCHAR2','NVARCHAR2');
spool off
@/tmp/&1._&2..sql
set termout on heading on feed on timing on verify on

[email protected]@orcl> @colpp_table CBLEILE T1

I.e. a temporary script /tmp/<owner>_<table_name>.sql gets written with format-commands for all VARCHAR2 (and NVARCHAR2) columns of the table. That temporary script is automatically executed:

[email protected]@orcl> !cat /tmp/CBLEILE_T1.sql

column OWNER format a5
column OBJECT_NAME format a36
column SUBOBJECT_NAME format a2
column OBJECT_TYPE format a10
column TIMESTAMP format a20
column STATUS format a7
column TEMPORARY format a2
column GENERATED format a2
column SECONDARY format a2
column EDITION_NAME format a1
column SHARING format a14
column EDITIONABLE format a2
column ORACLE_MAINTAINED format a2
column APPLICATION format a2
column DEFAULT_COLLATION format a4
column DUPLICATED format a2
column SHARDED format a2

Now the formatting looks much better without having to format each column manually:

[email protected]@orcl> @colpp_table CBLEILE T1
[email protected]@orcl> select owner, oracle_maintained, object_name from t1 where rownum < 4;

OWNER OR OBJECT_NAME
----- -- ------------------------------------
SYS   Y  TS$
SYS   Y  ICOL$
SYS   Y  C_FILE#_BLOCK#

3 rows selected.

But what happens when selecting from a view? The column avg_col_len in all_tab_columns is NULL for views:

[email protected]@orcl> create view t1v as select * from t1;
[email protected]@orcl> column column_name format a21
[email protected]@orcl> select column_name, avg_col_len from all_tab_columns where owner=user and table_name='T1V';

COLUMN_NAME           AVG_COL_LEN
--------------------- -----------
EDITIONABLE
ORACLE_MAINTAINED
APPLICATION
DEFAULT_COLLATION
DUPLICATED
SHARDED
CREATED_APPID
CREATED_VSNID
MODIFIED_APPID
MODIFIED_VSNID
OWNER
OBJECT_NAME
SUBOBJECT_NAME
OBJECT_ID
DATA_OBJECT_ID
OBJECT_TYPE
CREATED
LAST_DDL_TIME
TIMESTAMP
STATUS
TEMPORARY
GENERATED
SECONDARY
NAMESPACE
EDITION_NAME
SHARING

26 rows selected.

My idea was to do the following: Why not taking the “bytes”-computation per column from the optimizer divided by the number of rows returned by the view? I.e.

[email protected]@orcl> explain plan for select owner from t1v;

Explained.

[email protected]@orcl> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     | 67944 |   331K|   372   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   | 67944 |   331K|   372   (1)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

Now I just calculate the Bytes/Rows. As I just select 1 column I do get approximately the avg_col_len with that:

[email protected]@orcl> select 331000/67944 from dual;

331000/67944
------------
  4.87165901

That value is close to the avg_col_len statistic of the underlying table:

[email protected]@orcl> select avg_col_len from user_tab_columns where table_name='T1' and column_name='OWNER';

AVG_COL_LEN
-----------
          5

So the remaining question was just where to get the bytes and cardinality computation from? It’s in the plan_table:

[email protected]@orcl> select bytes, cardinality, ceil(bytes/cardinality) avg_col_len from plan_table where id=0;

     BYTES CARDINALITY AVG_COL_LEN
---------- ----------- -----------
    339720       67944           5

With that information I had everything to create a script colpp_explain.sql:

[email protected]@orcl> !more colpp_explain.sql
set termout off heading off lines 200 pages 999 trimspool on feed off timing off verify off
set serveroutput on size unlimited
spool /tmp/&1._&2..sql
declare
   avg_col_len number;
begin
   for i in (select column_name from all_tab_columns where owner=upper('&1.') and table_name=upper('&2.') and data_type in ('VARCHAR2','NVARCHAR2')) loop
      delete from plan_table;
      execute immediate 'explain plan for select '||i.column_name||' from &1..&2.';
      select ceil(bytes/cardinality) into avg_col_len from plan_table where id=0;
      dbms_output.put_line('column '||i.column_name||' format a'||to_char(avg_col_len+1));
   end loop;
end;
/
spool off
@/tmp/&1._&2..sql
set termout on heading on feed on timing on verify on
set serveroutput off

I.e. I’m looping through all columns with type VARCHAR2 (or NVARCHAR2) of the view and do an

explain plan for
select <column> from <view>;

With that information I can compute the avg_col_len by dividing the bytes by the cardinality and add the column formatting command to a script, which I finally execute.

[email protected]@orcl> @colpp_explain CBLEILE T1V
[email protected]@orcl> !cat /tmp/CBLEILE_T1V.sql
column EDITIONABLE format a3
column ORACLE_MAINTAINED format a3
column APPLICATION format a3
column DEFAULT_COLLATION format a5
column DUPLICATED format a3
column SHARDED format a3
column OWNER format a6
column OBJECT_NAME format a37
column SUBOBJECT_NAME format a3
column OBJECT_TYPE format a11
column TIMESTAMP format a21
column STATUS format a8
column TEMPORARY format a3
column GENERATED format a3
column SECONDARY format a3
column EDITION_NAME format a67
column SHARING format a15

[email protected]@orcl> select owner, oracle_maintained, object_name from t1v where rownum < 4;

OWNER  ORA OBJECT_NAME
------ --- -------------------------------------
SYS    Y   TS$
SYS    Y   ICOL$
SYS    Y   C_FILE#_BLOCK#

3 rows selected.

To use a single script for tables and views I created a simple wrapper-script around colpp_table.sql and colpp_explain.sql:

[email protected]@orcl> !cat colpp.sql
set termout off heading off lines 200 pages 999 trimspool on feed off timing off verify off
define var=colpp_explain.sql
column objt new_value var
select decode(object_type,'TABLE','colpp_table.sql','colpp_explain.sql') objt from all_objects where owner=upper('&1.') and object_name=upper('&2.');
@@&var. &1. &2.
set termout on heading on feed on timing on verify on

I.e. if the parameter is a table-name I do call colpp_table.sql. Otherwise I call colpp_explain.sql.

Finally it looks as follows:

For the table:

[email protected]@orcl> @colpp CBLEILE T1
[email protected]@orcl> select owner, oracle_maintained, object_name from t1v where rownum < 4;

OWNER OR OBJECT_NAME
----- -- ------------------------------------
SYS   Y  TS$
SYS   Y  ICOL$
SYS   Y  C_FILE#_BLOCK#

For the view:

[email protected]@orcl> @colpp CBLEILE T1V
[email protected]@orcl> select owner, oracle_maintained, object_name from t1v where rownum < 4;

OWNER  ORA OBJECT_NAME
------ --- -------------------------------------
SYS    Y   TS$
SYS    Y   ICOL$
SYS    Y   C_FILE#_BLOCK#

I.e. with a call to colpp.sql I can format all VARCHAR-columns of a table or a view. It’s of course not perfect, but easy, quick and better than the default-settings. You may even extend the scripts to also provide the heading per column or specify a sql_id or a script as parameters to colpp.sql.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Clemens Bleile
Clemens Bleile

Technology Leader & Principal Consultant