2 Replies Latest reply: Jul 12, 2012 10:04 PM by Frank Kulash RSS

    Adjust SQL*Plus Display Formatting

    895327
      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
          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
            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