SQL*Plus console, column widths with selects
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).
Use column command to set width. Following sets width to 20 characters.
column external_name format a20
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
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
SELECT 'COLUMN ' || COLUMN_NAME || ' FORMAT A'||&width
WHERE TABLE_NAME = UPPER('&table');
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> 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> 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;
With a little tweaking you could probably get rid of any of the output you don't want to see.
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.
Click Here to Expand Forum to Full Width