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.

Possibly related:

Tagged , | Post a Comment