News, views, tips and tricks on Oracle and other fun stuff

Oracle SQLPlus

Even though I use Sql Navigator at work, I also often use SQLPlus. Almost everyone who has access to an Oracle database, has access to SQL*Plus. On the other hand, not everyone has SQL Navigator or similar third party tools installed on his/her machine.

SQLPlus can be started in GUI mode (Windows only) or in command-line mode. I like the command-line mode because it is more flexible and can be used on any platform.

By default, when you start SQLPlus, the prompt changes to SQL>:

C:\>sqlplus scott/tiger

You can easily change the prompt if you like, just use set sqlprompt to whatever you like:

SQL> set sqlprompt 'command >'
command >

But you want to set the sql prompt to be something like “current user @ database name”, so you may do something like this:

command >column user_sid new_value sql_prompt
command >select
  2      lower(user) || '@' ||
  3      substr(global_name, 1,
  4      decode(instr(global_name, '.'),
  5          0, length(global_name),
  6          instr(global_name, '.') - 1)
  7      ) user_sid
  8  from global_name
  9  /


command >set sqlprompt '&sql_prompt> '

Cool. However, in Oracle 9.2 and above, you can get rid of the “global_name” table and use the all new _CONNECT_IDENTIFIER DEFINE Variable. This new variable contains the SID as supplied by the user. This allows the connection information to be accessed like any other DEFINE variable. Let’s modify the above to make use of this new feature:

scott@EDDEV> set sqlprompt 'command >'
command >column user_sid new_value sql_prompt
command >select lower(user) || '@' || 
  2      '&_CONNECT_IDENTIFIER' user_sid from dual;


command >set sqlprompt '&sql_prompt> '

Much simpler.

You may already know that if you create a file named login.sql and put it in the same folder where you launch SQLPlus, that file, login.sql, will always execute once at the time you execute the command “sqlplus”. Great! Let’s put our SQL prompt initialization in login.sql, we can also throw in some extra SETtings. Here is the content of my login.sql:

set term off
set serveroutput on size 1000000 format wrapped
set long 5000
set linesize 131
set trimspool on
set pagesize 9999
define sql_prompt=idle
column user_sid new_value sql_prompt
select lower(user) || '@' || 
'&_CONNECT_IDENTIFIER' user_sid from dual;
set sqlprompt '&sql_prompt> '
set timing on
set term on

Let’s try it:

H:\SQLplus>sqlplus scott/tiger
scott@eddev> disconnect

OOPS! When I disconnected the sql prompt remained the same. To solve this issue, we have to execute login.sql whenever we connect and disconnect. Easy enough. Let’s create a connect.sql and disconnect.sql and put them in the same place where login.sql is:


 set term off
 connect &1 


 set term off

The only extra thing you need to do now is to use @disconnect and @connect instead of disconnect and connect. Let’s test:

H:\SQLplus>sqlplus /nolog
idle> @connect scott/tiger
scott@eddev> @disconnect


Filed in Oracle on 04 Aug 05 | Tags: ,

Reader's Comments

  1. |

    Great article! It works!

    I put mine in glogin.sql.

    Related links:

    Jonathan Lewis’ and his FAQ:

    Oracle SQL*Plus FAQ:

    Also, “Setting Up” (Chapter 0) from Tom Kyte’s “Expert One-on-One Oracle” and, naturally, the SQL*Plus User’s Guide and Reference, available from here:

  2. |

    Great links as usual. Thanks Robert.

  3. |

    In 10g, it gets even easier:

    set sqlprompt '_user@_connect_identifier > '

    SQL*Plus Command Reference

  4. |

    Thanks for the tip Alex. Another reason to upgrade to 10g :)

  5. |

    […] Related links: My other blog entry about SQLPlus Related entry at Tom’s blog SQLPlus® User’s Guide and Reference   […]

  6. |


    I am tring to insert bulk sql statements from syslog-ng into oracle and I find there are more thn 50% lose.

    syslog daemon writes “insert statements” to a FIFO pipe, from this pipe another daemon attempts to push insert statements to sqlplus CLI.

    syslog-ng daemon config

    writes to FIFO pipe /var/run/ora.pipe

    destination d_oracle { pipe(“/var/run/ora.pipe” template(“ALTER SESSION SET SQL_TRACE = true;\nINSERT INTO syslogentry (hostname, facility, SEVERITY, MSG_SENT_TIME,p rogram, message,MSG_RCV_TIME) VALUES ( ‘$HOST’,’$FACILITY’, ‘$PRIORITY’, to_date(‘$MONTH $DAY$ $YEAR $HOUR:$MIN:$SEC’, ‘mm dd yyyy hh24:mi:ss’), ‘$PROGRAM’, ‘$MSGONLY’,SYSTIMESTAMP);\ncommit;\n”) template-escape(yes)); };

    we have tried to compare number of event (insert statements) coming on this pipe by writing another script that writes to file.

    pipe to db script


    nohup sqlplus user/pass < /var/run/ora.pipe >> /home/oracle/log.err

    pipe to file script


    nohup cat /var/run/ora.pipe > /logs/debug.log

    number of events in files are almost 10 times more than going in db within a given time slot.

    can any one help me ?

    Regards Sumit

  7. |

    Is it possible to add color to the prompt?

    I want different color’s for different environments that I use sqlplus on to distinguish them a bit and also add a bit of color to the whole black and white shell.

    I tried using control characters that work on the OS I’m using but they get simply printed as is.

    Anyone know a wrapper that I can use?

  8. |

    Pradeep, this is the first time I’ve ever heard that you can even have a colorful SQLPlus prompt! I do question the need for color though. That won’t work well with color blind people.

  9. |

    Pradeep, yes I got colors in sqlplus. It’s done via vi’s Ctrl-v to write the escape sequences to the login.sql, and also, the escape sequence is supported by xterm, which is what I use putty to connect to a Linux box(I guess on Windows it would be a different way to do it or just can’t be done). My sqlprompt variable looks like this: ^[[32;47m&_user@&_connect_identifier^[[30m>^[[0;49m Copy & paste won’t work, you should use vi or other editor which is capable of inputing escape sequences instead to get the ‘^[‘s to the file.

  10. |

    How to turn on colors?

  11. |

    May i ask about Views in Oracle in this forum?

    thank you -fariza-

  12. |

    FARIZA, You can. However, I suggest you use

  13. |

    Wonderful, colors worked! Good great tipe! Thanks. But also I needed the host name to be in my prompt. Any help is greatly appreciated. thanks Partha