CODEWITHSUNDEEP
oracle-databasesqlplus
MooHa
MooHa

Reputation: 849

Oracle Column width for all columns

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..

enter image description here

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

Upvotes: 12

Views: 53777

Answers (8)

Harry
Harry

Reputation: 11

Best solution

set markup csv on

Upvotes: 1

laktak
laktak

Reputation: 60003

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.

Upvotes: 0

Vineet Singh
Vineet Singh

Reputation: 347

remove set from the statement:

COLUMN FORENAME FORMAT A10

SET COLUMN SURNAME FORMAT A10

Upvotes: -1

miracle173
miracle173

Reputation: 1973

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.

Upvotes: 0

shelley
shelley

Reputation: 7324

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.

Upvotes: 0

René Nyffenegger
René Nyffenegger

Reputation: 40489

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.

Upvotes: 0

Kirill Leontev
Kirill Leontev

Reputation: 10931

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.

Upvotes: 3

David Aldridge
David Aldridge

Reputation: 52336

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.

Upvotes: -1

Related Questions