3 Replies Latest reply: Oct 11, 2012 3:59 PM by EdStevens RSS

    Configure SQL*Plus

    895327
      Learning how Oracle works has been a huge feat for me but now I'm getting frustrated with using SQL*Plus. Currently I have no experience with SQL*Plus and I have read the documentation:

      http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#BACGAJIC

      My question is I'm looking for advice and or tips on how to set up SQL*Plus to render data I retrieve with the SELECT statement more coherently. I'm using 11.2.0.3 and from a default installation, it doesn't seem to have a rhyme or reason for how it formats the text on my SQL*Plus client. It's just a mess and extremely hard for me to read or understand. For example:
      SQL> SELECT *
        2  FROM CMENNENS.USERS
        3  WHERE USERS_NAME = 'CARLOS MENNENS'
        4  ;
      
        USERS_ID USERS_NAME
      ---------- --------------------------------------------------
      USERS_EMAIL
      --------------------------------------------------------------------------------
      USERS_DOB
      ---------
            1000 CARLOS MENNENS
      CARLOS@TEST.TLD
      As you can see the coorisponding data doesn't flow underneath the column heading for whatever reason and it makes this hard to read once tables get larger in size. I was told to do the following:
      SQL> set linesize 32765;
      SQL> set pagesize 50;
      The above helped a little but it still looks weird or just not formatted as I expect it to. I really am no expect obviously so reading the docs on something like this is hard. It's not like I want to accomplish 'X' so I read the docs and configure the system to do 'X'. I have no idea what 90% of those functions in the doc ID listed actually will do or break and or their default values. Does anyone know exactly what I should change or look at in order to get some thing that looks as follows:
      zoo=# SELECT *
      zoo-# FROM users
      zoo-# WHERE users_name LIKE 'Carlos%'
      zoo-# ORDER BY users_id, users_name
      zoo-# ;
       users_id |   users_name   |   users_email   | users_dob
      ----------+----------------+-----------------+------------
           1001 | Carlos Mennens | carlos@test.tld | 1979-05-25
      (1 row)
        • 1. Re: Configure SQL*Plus
          Sven W.
          Hi Carlos,

          William who is also a regular here on the forum has a nice article about setting up sql*plus: http://www.williamrobertson.net/documents/sqlplus_setup.html

          I suggest not to use sql*plus, but to use oracle sql developer. It is free and has a modern look and feel. However sometimes there is still the need to do something in sql*plus. So there is no harm learning more about the tool. E.g. when using it to spool data to a file, etc.

          To increase how text is printed I usually set linesize AND longchuck size to a higher value.
          Seems to work for me at least.
          set linesize 400
          set longc 400
          set pagesize 100
          The size of a column depends on the possible size not upon the actual data that is in it.

          So a varchar2(30) column will be rendern with 30 blank chars if there is nothing in it.
          A Varchar2(300) column will have 300 blanks. This really messes up the screen.

          An option is to format the column for a query in a way you want it.

          The syntax would be
          col user_name format "A10" 
          This would format any column "USER_NAME" with a size of 10 alphanumerical chars. Doc: http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve013.htm#SQPUG034

          Regards
          Sven

          Edited by: Sven W. on Oct 11, 2012 7:58 PM

          Edited by: Sven W. on Oct 11, 2012 7:59 PM

          Edited by: Sven W. on Oct 11, 2012 8:05 PM -- added doc link for column command
          • 2. Re: Configure SQL*Plus
            JustinCave
            You can control how much horizontal space SQL*Plus uses to display each column using the `COLUMN` command, i.e.
            SQL> column users_id format 99999999
            SQL> column users_name format a20
            SQL> column users_email format a20
            SQL> column users_dob format a10
            This will allocate 20 spaces for users_name and users_email, 10 spaces for users_dob, and display up to 8 digits of users_id. Of course, this may force the data on a particular row to wrap around to the next row if it exceeds whatever value you specified.

            In general, though, it is probably easier to use SQL Developer if you are just trying to interactively run some queries. A GUI like SQL Developer is generally going to present the data in a much more human-friendly format with much less work on your part to adjust the formatting.

            Justin
            • 3. Re: Configure SQL*Plus
              EdStevens
              CarlosinFL wrote:
              Learning how Oracle works has been a huge feat for me but now I'm getting frustrated with using SQL*Plus. Currently I have no experience with SQL*Plus and I have read the documentation:

              http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#BACGAJIC

              My question is I'm looking for advice and or tips on how to set up SQL*Plus to render data I retrieve with the SELECT statement more coherently. I'm using 11.2.0.3 and from a default installation, it doesn't seem to have a rhyme or reason for how it formats the text on my SQL*Plus client. It's just a mess and extremely hard for me to read or understand. For example:
              SQL> SELECT *
              2  FROM CMENNENS.USERS
              3  WHERE USERS_NAME = 'CARLOS MENNENS'
              4  ;
              
              USERS_ID USERS_NAME
              ---------- --------------------------------------------------
              USERS_EMAIL
              --------------------------------------------------------------------------------
              USERS_DOB
              ---------
              1000 CARLOS MENNENS
              CARLOS@TEST.TLD
              As you can see the coorisponding data doesn't flow underneath the column heading for whatever reason and it makes this hard to read once tables get larger in size. I was told to do the following:
              SQL> set linesize 32765;
              SQL> set pagesize 50;
              The above helped a little but it still looks weird or just not formatted as I expect it to. I really am no expect obviously so reading the docs on something like this is hard. It's not like I want to accomplish 'X' so I read the docs and configure the system to do 'X'. I have no idea what 90% of those functions in the doc ID listed actually will do or break and or their default values. Does anyone know exactly what I should change or look at in order to get some thing that looks as follows:
              zoo=# SELECT *
              zoo-# FROM users
              zoo-# WHERE users_name LIKE 'Carlos%'
              zoo-# ORDER BY users_id, users_name
              zoo-# ;
              users_id |   users_name   |   users_email   | users_dob
              ----------+----------------+-----------------+------------
              1001 | Carlos Mennens | carlos@test.tld | 1979-05-25
              (1 row)
              What you are seeing is simple line wrap. It's caused by either or both of
              1) the LINESIZE setting in sqlpus
              2) the dimensions of the terminal/window in which you are running sqlplus.

              As others have pointed out, you could use sql developer. I'm a bit of a a dinosaur in that I still use sqlplus for most stuff. The nice thing about that is it is not trying to be smarter than you. You get exactly what you ask for. I handle this line wrap issue in two ways:

              1) use of the COLUMN ... FORMAT, as others have alluded to. If you have a column that is much wider than the typical data in it, you can really shorten it up. For instance, when querying dba_tables, OWNER is 30 bytes, but I seldom have an OWNER value actually over 10, so "COL OWNER FORMAT A10" shortens that column to 10 characters in my output. Anything that is longer, and the individual column will wrap, not the entire line.

              2) always spool my output then view the spooled file. All of my ad-hoc sql files look like this
              -- file: doit.sql
              set trimspool on 
              set linesize 512
              set pages 90
              spool doit.log
              --
              -- put my query here
              --
              spool off
              edit doit.log
              then within sqlplus I simply
              SQL> @doit
              The query runs and the result immediately pops up in the system default editor. This works a bit better on Windows where the editor is notepad. That gives me a scalable and scrollable window with no enforced line wrap. Just be aware that while you are looking at the spooled file, sqlplus is still processing your script, waiting for you to exit the editor so it (sqlplus) can process the next line of the script. If you actually edit and save the script before you exit the spool file, you can cause sqlplus to find a 'next' line when you didn't intend for it to.