Question:
I want to setup the working environment in SQL*Plus automatically, e.g. to set an prompt that reflects the Oracle SID, choose my favourite editor when I type 'ed', and set up dbms_output to work.
Answer:
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 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 examin the environment variable SQLPATH and walk the path until it finds the first directory containing a version of 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 user rollback segment' 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.
(Comment - 29th Nov) 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 SYS, 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.
Sample glogin.sql script
set serveroutput on size 1000000 set pagesize 24 set linesize 120 column segment_name format a32 column column_name format a32 column plan_plus_exp format a90 set long 20000 set trimspool on define _editor=vi set termout off column prompter new_value m_prompt ` select host_name || ':' || instance_name || '>' prompter from v$instance; set sqlprompt '&m_prompt' undefine m_prompt commit; set termout on