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

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

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  /

USER_SID
---------------------------------------------
scott@EDDEV

command >set sqlprompt '&sql_prompt> '
scott@EDDEV>

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;

USER_SID
------------------------------------
scott@eddev

command >set sqlprompt '&sql_prompt> '
scott@eddev>

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
scott@eddev>

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:

Connect.sql

 set term off
 connect &1 
 @login

Disconnect.sql

 set term off
 disconnect
 @login

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

Perfect.


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: http://www.jlcomp.demon.co.uk/faq/sqlplus_prompt.html

    Oracle SQL*Plus FAQ: http://www.orafaq.com/faqplus.htm

    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: http://www.oracle.com/technology/documentation/index.html

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

    Hi

    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

    !/bin/sh

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

    pipe to file script

    !/bin/sh

    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 http://forums.oracle.com

  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