Author's name: Jonathan Lewis
Author's Email: Jonathan@jlcomp.demon.co.uk
Date written: 26th July 2001
Oracle version(s): 7.3 – 10.2
I want to setup the working environment in SQL*Plus automatically, e.g. to set a prompt that reflects the Oracle SID, choose my favourite editor when I type 'ed', and set up dbms_output to work.
When SQL*Plus starts up, it looks for two files - glogin.sql and login.sql.
The glogin.sql must be in the directory $ORACLE_HOME/sqlplus/admin and will be run as soon as the connction to the database is complete.
Once the glogin.sql file has been executed, the login.sql file will be executed, but the location of the login.sql file is rather more flexible. It depends on the current working directory (where you started SQL*Plus) and the operating system environment variable SQLPATH.
If there is a login.sql in the local directory, it will be executed. If there is no local file, then Oracle will examine the environment variable SQLPATH and walk the path until it finds the first directory containing a file called login.sql and will execute that script (and then stop walking the path).
Remember that the login.sql can overwrite the actions of glogin.sql.
Watch out too for the fact that batch jobs may wish to do things that are incompatible with the features you want to set up automatically - so be cautious about changing the glogin.sql in particular. One point you may note especially in the sample below is the commit; that occurs at the end of the script. Because I have a select statement in the script, I have included this commit because of historical problems where even a select statement would stop set transaction read only and set transaction use rollback segment XXX working because they weren't the first statement in a transaction. In Oracle 8.1.5 this doesn't seem to be a problem any more, but earlier versions may need this line.
Note: If you want to copy this script, make sure that every user has the privilege to select from the v$instance dynamic performance view (from the SYS schema, grant select on v_$instance to public should do). You may find on some versions and platforms that you get an otherwise inexplicable hang as you connect from SQL*Plus.
Note: Niall Litchfield has pointed out that one major deficiency in this approach is that if a user does a connect user/password then the glogin.sql script is not re-executed if you are running versions earlier than 10g. Depending what you have put into the prompt this could leave the user with a completely misleading prompt.
Sample glogin.sql script
set serveroutput on size 1000000 format wrapped
set pagesize 24
set linesize 120
set trimspool on
column segment_name format a32
column column_name format a32
column plan_plus_exp format a90
set long 20000
set termout off
column prompter new_value m_prompt
host_name || ':' || instance_name || '>' prompter
set sqlprompt '&m_prompt'
set termout on
Further reading: N/A