#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    56
    Rep Power
    3

    Configure SQL*Plus


    One of my biggest struggles learning / cutting my teeth on Oracle 11g has been how SQL*Plus is configured on a standard / default installation. Mainly I'm trying to understand how I can have it work more or less like 'psql' utility that ships with PostgreSQL. Obviously most Oracle dudes will roll their eyes at my last statement and I know psql is not SQL*Plus but I'm just looking for a way in SQL*Plus that I can do the following:

    - up / down arrow for previous SQL command history, type ahead with the tab function, and also clearly list things in a logical easy to read order.

    *The last one being the biggest annoyance for me.*

    The hardest part for me is how SQL*Plus outputs data for me when I run a SELECT statement query on a table. It doesn't seem to have any specific rhyme or reason when it displays the data. It seems all out of order in accordance to the table order as shown below:

    Code:
    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@IAMUNIX.COM
    As you can see the ouput is IMO an complete mess and very hard to follow. I'm used to PostgreSQL's 'psql' utility which neatly organizes your query output cleanly underneath it's specific column name. Is there a tutorial or site that can help me configure / setup SQL*Plus to neatly organize my SQL output under the designated column names as well as allow me to call back previous commands with the up / down arrows on my keyboard as well as tab auto-completion for commonly used SQL statements like SELECT, UPDATE, DELETE, COUNT, CREARE, etc etc etc...

  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    To fix the display width, use:
    Code:
    set linesize 32765;
    By default SQL*Plus will repeat the header every 25 lines, to change that use
    Code:
    set pagesize 50
    The up/down arrows work for me under Windows. I think the missing editing capabilities is a Unix problem. There are some hacks out there to fix that. Just search for "sqlplus commandline edit linux" (or something similar).

    But I do agree: working with psql is much more user-friendly.

    You might want to go through the list of available options:
    http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#BACGAJIC
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    If possible, Use a sql client like: Sql Developer(oracle), Toad, PL/SQL Developer, Aqua Data Studio, etc.
    You will like the interface/tools a lot better.

IMN logo majestic logo threadwatch logo seochat tools logo