Oracle Column width for all columns

Question

One of the issue when executing a long statement for displaying various columns for example

 select g.guestid, g.forename, g.surname, b.bookingid,
 b.arrivedate, b.departdate, br.floorno, br.roomno from...

the column sizing on linux terminal seems to be an issue. For example the Forename VarChar(80) column takes up much of the width of the screen when executing the above statement and one way to cut it down would be through:

SET COLUMN FORENAME FORMAT A10

for example. However, many columns would need to be repeatedly go through this which is quite long. i.e.

SET COLUMN FORENAME FORMAT A10
SET COLUMN SURNAME FORMAT A10

and so on...

Is there a way to say adjust column width according to text width so that every fits in nicely. and not like this..

I would prefer some solution that does not involve the use of FUNCTIONS.

Answer 1

No, there is no simple way to make SQL*Plus "auto-adjust" column width to text width.

However, scripting can make your life easier.

First of all, reduce typing. Don't do SET COLUMN FORENAME FORMAT A10, do something like @c forename 10 instead.

17:33:31 SYSTEM@dwal> cl col
columns cleared
17:33:33 SYSTEM@dwal> select * from dual;

D
-
X

Elapsed: 00:00:00.01
17:33:37 SYSTEM@dwal> @c dummy 10
17:33:43 SYSTEM@dwal> select * from dual;

DUMMY
----------
X

Elapsed: 00:00:00.01
17:33:45 SYSTEM@dwal> get s:\c.sql
  1* col &1. for a&2.
17:33:50 SYSTEM@dwal>

Or quickly hiding wide columns like this:

17:48:44 SYSTEM@dwal> select owner, table_name from all_tables where rownum = 1;

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SYS                            CON$

Elapsed: 00:00:00.24
17:48:49 SYSTEM@dwal> @np owner
17:48:53 SYSTEM@dwal> select owner, table_name from all_tables where rownum = 1;

TABLE_NAME
------------------------------
CON$

Elapsed: 00:00:00.26
17:48:56 SYSTEM@dwal> get s:\np
  1  col &1 noprint
  2* @undef

These are just a two of many scripts I use on a daily basis. This approach takes takes time and some personal attention to customization to get used to it and make it effective, but reduces the amount of keys you press dramatically.

Second, there is glogin.sql. It is a script that executes every time you connect somewhere. I assume you know a list of "long" columns that make your lines wrap.

Just list them there, and your

SET COLUMN FORENAME FORMAT A10
SET COLUMN SURNAME FORMAT A10

column parameters would be set each time you (re)connect.