Re: sqlplus...automatically setting a column width?

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Fri, 30 Jun 2006 06:18:18 -0400
Message-ID: <GPWdnZug_bb2ZznZnZ2dnUVZ_sudnZ2d@comcast.com>

"Mark Harrison" <mh_at_pixar.com> wrote in message news:C73pg.75799$4L1.34059_at_newssvr11.news.prodigy.com...
:I have some really wide varchar columns, but the data
: is not very long. Is there a way to automatically
: combine these two statements, to set the column width
: to the width of the longest string?
:
: SQL> select max(length(x2)) from foo;
:
: MAX(LENGTH(X2))
: ---------------
: 32
:
: SQL> column x2 format a32
:
: Many TIA!
: Mark
:
: --
: Mark Harrison
: Pixar Animation Studios

doable, but not very efficient, as you would have to process the data twice

here's and example (you would include the SET TERMOUT OFF/ON commands in your script file, and they only habe an effect when running commands from a file -- this example, of course, has some extra commands for illustration that you would not include in your script file)

SQL> select first_name
  2 from employees
  3 where first_name like 'M%';

FIRST_NAME

---

Mozhe
Michael
Mattea
Michael
Martha
Matthew

6 rows selected.

SQL> SET TERMOUT OFF SQL> select 'a'||max(length(first_name)) as max_fn   2 from employees
  3 where first_name like 'M%';

MAX_FN

---

a7

SQL> col first_name format &w_fn

SQL> SET TERMOUT ON SQL> col first_name

COLUMN first_name ON
FORMAT a7

SQL> select first_name
  2 from employees
  3 where first_name like 'M%';

FIRST_N

---

Mozhe
Michael
Mattea
Michael
Martha
Matthew

6 rows selected.

SQL> ++ mcs Received on Fri Jun 30 2006 - 05:18:18 CDT