SQL*Plus console, column widths with selects

[Gus]

In a server manager console session I can use charwidth (e.g. SET CHARWIDTH 20) to control the width of columns from the output of a select statement. Can someone please tell me what is the equivalent in an SQL*Plus console session. E.G. I am selecting from dba_users and because the last column EXTERNAL_NAME column is VARCHAR2(4000) therefore the subsequent wrapping messes up the output. Setting linesize only works to a point. The SET CHARWIDTH command is not recognised as a valid command in sqlplus it seems (console version).

Many thanks
Gus

[patel_dil]

Use column command to set width. Following sets width to 20 characters.

column external_name format a20

HTH,

[Gus]

Yes I am aware of this command but having to type this for each column for each table where wrapping is likely to occur is mighty tedious. There must be a better way

Thanks anyway
Gus

[rigatoni]

How about running a sqlplus script to do the formatting commands and then running your query. Something like:

Here's the script that does the formatting. I called it format.sql.

accept table prompt 'Enter table name: '
accept width default 20 prompt 'Enter column width: '

SET PAGES 0
SET LINES 333
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADSEP OFF
SET HEADING OFF


SPOOL C:\TEMP\temp.sql
SELECT 'COLUMN ' || COLUMN_NAME || ' FORMAT A'||&width
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('&table');
SPOOL OFF

@@C:\TEMP\temp.sql

host del c:\temp\temp.sql

SET ECHO ON
SET VERIFY ON
SET FEEDBACK ON
SET TERMOUT ON
SET HEADSEP ON
SET HEADING ON
SET PAGES 25
SET LINES 60
SET TRIMSPOOL OFF


And the results:


sndev@AMSAA> @c:\temp\format.sql
sndev@AMSAA> accept table prompt 'Enter table name: '
Enter table name: dba_users
sndev@AMSAA> accept width default 20 prompt 'Enter column wid
Enter column width:
sndev@AMSAA>
sndev@AMSAA> SET PAGES 0
sndev@AMSAA> SET LINES 333
sndev@AMSAA> SET TRIMSPOOL ON
sndev@AMSAA> SET ECHO OFF
COLUMN USERNAME FORMAT A20
COLUMN USER_ID FORMAT A20
COLUMN PASSWORD FORMAT A20
COLUMN ACCOUNT_STATUS FORMAT A20
COLUMN LOCK_DATE FORMAT A20
COLUMN EXPIRY_DATE FORMAT A20
COLUMN DEFAULT_TABLESPACE FORMAT A20
COLUMN TEMPORARY_TABLESPACE FORMAT A20
COLUMN CREATED FORMAT A20
COLUMN PROFILE FORMAT A20
COLUMN INITIAL_RSRC_CONSUMER_GROUP FORMAT A20
COLUMN EXTERNAL_NAME FORMAT A20

sndev@AMSAA> SET VERIFY ON
sndev@AMSAA> SET FEEDBACK ON
sndev@AMSAA> SET TERMOUT ON
sndev@AMSAA> SET HEADSEP ON
sndev@AMSAA> SET HEADING ON
sndev@AMSAA> SET PAGES 25
sndev@AMSAA> SET LINES 60
sndev@AMSAA> SET TRIMSPOOL OFF



Now run the query:


sndev@AMSAA> select username, external_name from dba_users;

USERNAME EXTERNAL_NAME
-------------------- --------------------
SYS
SYSTEM
DBSNMP
SCOTT
CTXSYS
XDB
WMSYS



With a little tweaking you could probably get rid of any of the output you don't want to see.

[Gus]

Thanks Rigatoni, did think of creating a similar script and just ammend it whenever I came across columns that caused wrapping. Many thanks for the script, puzzled as to why something like charwidth is not incorporated into sqlplus console. Shall probably write a script to extract all columns from dict_columns & dba_tab_columns with varchar2 > 100 and add them to your script above and append to login.sql, better than nothing i guess.

Thanks
Gus