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.

Korns

Try using GUI tools instead of SQLPlus. Terminal window is not suits well for datagrid displaying. – Tavey

@EgorSkriptunoff: depends on the commandline client ;) SQL*Plus is really annoying regarding this. – Diamagnetism

Yeah, Spark's dataframes are shown beautifully on the terminal. – Vasileior

csvlook also does a much better job than SQLPlus. Using a GUI instead of a terminal doesn't fly with people like me :) – Jehoshaphat

@EgorSkriptunoff: until you want to save output and compare result with diff. – Jordaens

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.

Inopportune

Answer 2

Tom Kyte has written a print_table procedure, that displays the result set in a vertical fashion.

For example:

SQL> exec print_table('select g.guestid, g.forename, ... from ...')
GUESTID                       : 210
FORENAME                      : DINGLE
...
etc...
....
-----------------

PL/SQL procedure successfully completed.

This procedure comes in handy especially when the result set is small, ideally not more than maybe 10 rows.

Pliocene

Answer 3

The ansiconsole SQL format may be what you're looking for.

set sqlformat ansiconsole;
select g.guestid, g.forename, g.surname, b.bookingid,
   b.arrivedate, b.departdate, br.floorno, br.roomno from...

Or:

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

This format auto-sizes columns to based on the size of the query results.

Brahma

that is not sqlplus but SQLcl – Salomesalomi

Very good. Thank you. This may be SQLcl but it works in SQL*Developer with "F5" or "Run Script". Very helpful. Thank you again. – Salespeople

Answer 4

As already stated by other users there is no simple solution for sqlplus. Maybe it helps to use the glogin.sql or login.sql file (@René Nyffenegger provided an article about those files). You can put column definitions in these files. If you always query the same queries or if you have a consistent naming of your columns this may help.

Or you put the column statements in a script that you call by

@scriptname.sql

if you want to use the column format. You put the script in a directory that is part of the SQLPATH variable so that it can be called from any directory.

Or you use another tool. One user alredy pointed at SQLcl.

Salomesalomi

Answer 5

My workaround is to use CSV:

set markup csv on

Depending on the data and how many columns you have this may give you a readable output.

Otherwise just take the result and paste it into Excel or Visidata.

Analgesia

Answer 6

Best solution

set markup csv on

Heinrike

Answer 7

The session does not know what the maximum length of the strings to be returned will be until it has run the query. The column should be sized appropriately of course, but maybe you really are going to get a list of forenames that is 80 characters long -- if not then your data type length is too large.

As Eric says, GUI tools are better, and Oracle's SQL Developer is free and good.

Birthday

Answer 8

remove set from the statement:

COLUMN FORENAME FORMAT A10

SET COLUMN SURNAME FORMAT A10

Natika