This discussion is archived
2 Replies Latest reply: Jul 12, 2012 8:04 PM by Frank Kulash RSS

Adjust SQL*Plus Display Formatting

895327 Newbie
Currently Being Moderated
I'm new using SQL*Plus and I was wondering if there's a way I can adjust the way SQL*Plus displays information. I guess to start with I'm used to more simplistic tools like psql or mysql which auto adjust retrieved data in a clean / organized fasion to look at as follows from PostgreSQL:
webmail-# SELECT user_id, username, created FROM users;
 user_id | username |            created            
---------+----------+-------------------------------
       1 | carlos   | 2011-11-10 15:47:38.675578-05
As you can see from above, PSQL client cleanly displays the selected results for me but when I try in SQL*Plus, it's just a mess:
SQL> select * from CMENNENS.USERS
  2  order by USERS_ID;

  USERS_ID
----------
USERS_NAME
--------------------------------------------------------------------------------
USERS_EMAIL
--------------------------------------------------------------------------------
USERS_DOB
---------
      2636
David Smith
david.smith@gmail.com



  USERS_ID
----------
USERS_NAME
--------------------------------------------------------------------------------
USERS_EMAIL
--------------------------------------------------------------------------------
USERS_DOB
---------
      4765
Rachel Griffin
rachel.griffin@gmail.com
I am asking if there's a way I can adjust SQL*Plus to display the retrived data in verticle columns and horizontal rows? It appears to bunching all the column names verticly on top of each other and then the actual retrieved data isn't easily listed under it's assigned column. Can this be adjusted to any degree and if so, can someone please show me how?

Thank you!
  • 1. Re: Adjust SQL*Plus Display Formatting
    Ahmer Mansoor Journeyer
    Currently Being Moderated
    Please see

    http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch6.htm

    Rgds,

    Ahmer
  • 2. Re: Adjust SQL*Plus Display Formatting
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    By default, SQL*Plus allocates 10 spaces for displaying NUMBERS, and either 80 or the declared size (whichever is less) for VARCHAR2s.
    In your output:
    CarlosinFL wrote:
    SQL> select * from CMENNENS.USERS
    2  order by USERS_ID;
    
    USERS_ID
    ----------
    USERS_NAME
    --------------------------------------------------------------------------------
    USERS_EMAIL
    --------------------------------------------------------------------------------
    USERS_DOB
    ---------
    2636
    David Smith
    david.smith@gmail.com
    
    
    
    USERS_ID
    ----------
    USERS_NAME
    --------------------------------------------------------------------------------
    USERS_EMAIL
    --------------------------------------------------------------------------------
    USERS_DOB
    ---------
    4765
    Rachel Griffin
    rachel.griffin@gmail.com
    It looks like users_name and users_email are VARCHAR2 (80), or maybe even longer, so SQL*Plus is reserving 80 spaces for them. Since those two columns are filling up an entire line of output (each) the NUMBER columns can't go on the the same line with them, and so they occupy a line of output apiece.
    On top of that, SQL*Plus has a very small default page size. It repeats the headers at the top of each page, but, in your case, it looks like the headers are taking up most of the page.
    I am asking if there's a way I can adjust SQL*Plus to display the retrived data in verticle columns and horizontal rows? It appears to bunching all the column names verticly on top of each other and then the actual retrieved data isn't easily listed under it's assigned column. Can this be adjusted to any degree and if so, can someone please show me how?
    Use the SQL*Plus COLUMN command to override the default allocation for columns, and use the SET PAGESIZE command to set a more reasonable page length. Try something like this:
    COLUMN  users_name      FORMAT  A20
    COLUMN  users_email     FORMAT  A30
    
    SET     PAGESIZE        100
    If you have a users_name that really is longer than 20 characters, or a users_email that is over 30, then that line will wrap to display the whole string.

    These settings will last until you end your session (or re-set them).
    You can put commands like this in your LOGIN.SQL file, so they will automatically be run whenever you start a new session.

    For more details, look up "COLUMN", "SET" and "LOGIN.SQL" in the SQL*Plus manual:
    http://docs.oracle.com/cd/E11882_01/server.112/e16604/index.htm

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points