SQL*Plus console, column widths with selects
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: SQL*Plus console, column widths with selects

  1. #1
    Join Date
    Aug 2002
    Location
    Brighton, England
    Posts
    93

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

    Many thanks
    Gus

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Use column command to set width. Following sets width to 20 characters.

    column external_name format a20

    HTH,
    -- Dilip

  3. #3
    Join Date
    Aug 2002
    Location
    Brighton, England
    Posts
    93
    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

  4. #4
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    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.

  5. #5
    Join Date
    Aug 2002
    Location
    Brighton, England
    Posts
    93
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width