sqlplus and its column output

During tuning or normal DBA activities one of the most annoying things is sqlplus with its default column output when running queries. I.e. even after setting a linesize of 1000 and a pagesize of 1000 the output may look as follows in your preferred terminal emulation (putty here):

Most people address this by using other tools like sqldeveloper (or sqlcl with sqlformat ansiconsole). However, a lot of people still use sqlplus.

How can you address the output-format-issue with sqlplus?
Actually you can format columns of course and specify only the columns you would like to see (instead of “select *”):


SQL> select schemaname, osuser from v$session where sid=(select sid from v$mystat where rownum=1);
 
SCHEMANAME OSUSER
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
CBLEILE oracle
 
SQL> column schemaname format a32
SQL> column osuser format a32
SQL> select schemaname, osuser from v$session where sid=(select sid from v$mystat where rownum=1);
 
SCHEMANAME OSUSER
-------------------------------- --------------------------------
CBLEILE oracle


But that’s annoying if you have to do it for a couple of columns for every sql-statement you interactively type in.

The better alternative is to use a terminal emulation, which allows horizontal scrolling. Tanel Poder describes it in his “AOT Preparation Session” on Youtube: https://www.youtube.com/watch?v=tC81PMO7ODw.
Interestingly horizontal scrolling is available on Windows for ages. I.e. using cmd.exe or the Powershell you can define a “Screen Buffer Size” for the “Width” of the Window (in Properties -> Layout). Best is to set it under “Defaults” so that it’s available every time you open a cmd.exe or Powershell window.

But what to use on e.g. Linux? Most terminal emulation software does not allow horizontal scrolling. Actually there are only very few terminal emulation products available, which allow it. One of the freely available products is terminator ( https://github.com/software-jessies-org/jessies/wiki/Terminator ). After downloading the rpm on my Oracle Enterprise Linux server I installed it as follows:


[root@localhost terminator]# rpm -i org.jessies.terminator.x86_64.rpm
error: Failed dependencies:
/usr/bin/ruby is needed by org.jessies.terminator-27.171.7083-2.x86_64
[root@localhost terminator]# yum install ruby
[root@localhost terminator]# rpm -i org.jessies.terminator.x86_64.rpm
[root@localhost terminator]# terminator


The prereqs for terminator are an installed Java Runtime Environment (JRE -> you may use the one provided within your ORACLE_HOME) and Ruby (as you can see above).
I started terminator once as root to install the necessary global terminfo. After that I can use it as e.g. the oracle-user:


[oracle@localhost ~]$ terminator &


There are no wrapped lines due to the terminal size anymore:

And you can scroll to the right:

Running sqlcl with sqlformat set to ansiconsole the Terminator terminal emulation also helps for queries with many columns, which do not fit on the screen:

Scrolled to the right:

Besides the formatting issue, it’s of course recommended to use the utility rlwrap (readline wrapper) with sqlplus on Linux for command line history (get previous command lines with the up and down key) and easy command line editing. Actually rlwrap is much better than the history available in sqlplus of 12.2. I do recommend watching the youtube video from Tanel Poder mentioned above, because he also shows that rlwrap can be configured to provide command completion (for Oracle PLSQL-objects and reserved words) as well.

When installing the dbi services DMK Management Kit ( https://www.dbi-services.com/dmk ) on Linux then rlwrap is of course included.

So sqlcl is cool, but sqlplus also still has its right to exist 😉