Login Scripts
This
section will be very brief, but highlights a
very useful
SQL*Plus facility that can often make
both SQL*Plus interactive sessions and scripting
less difficult. Note too that some of the prior
mentioned tools such as Toad and SQL Developer
support this SQL*plus feature, although with
some minor differences based upon Oracle
version. For instance, prior to Oracle 11g, the
login.sql
script fired only upon startup. Whereas in 11g
and Toad, it fires up each time a script is
executed.
As is covered in the
following sections, there will be times when
SQL*Plus will be used and issue a common set of
commands over and over again. Often these
commands will be those that help define the
environment and/or behavior for SQL*Plus during
that session. For example, the six SQL*Plus
commands below might well represent commands
that a user wants active anytime they enter
SQL*Plus or execute a SQL script.
SET TAB OFF
SET VERIFY OFF
SET LINESIZE 132
SET PAGESIZE 999
SET TRIMOUT ON
SET TRIMSPOOL ON
Obviously, typing these six commands each time
SQL*Plus is entered is not an optimal solution.
What about adding those six lines to the top of
each of the scripts? Actually, that is also not
good because if the commands are changed, then
the next step will be to edit all of the
scripts. The most logical idea is to simply
place those six lines within their own little
script
set_environment.sql,
and then simply call that script at the top of
all the other scripts, such as this:
set_environment.sql
script
SET TAB OFF
SET VERIFY OFF
SET LINESIZE 132
SET PAGESIZE 999
SET TRIMOUT ON
SET TRIMSPOOL ON
show_init_parms.sql script
@SET_ENVIRONMENT
col name
format a40
heading 'Name'
col value
format a80
heading 'Value'
select NAME, VALUE
from v$parameter
where value not like
'%\%'
and value not
like '%/%'
and name not
like 'log_archive_dest%'
order by name;
But guess what? SQL*Plus
already has a built-in mechanism to do this!
SQL*Plus supports the
concepts of automatically executed user and site
profile scripts. These are simply SQL script
files containing either SQL or SQL*Plus commands
that are executed automatically whenever
SQL*Plus is launched. Think of these files as
being like Microsoft DOS
autoexec.bat files
or a UNIX login.sh
script. These user and site profile scripts are
named login.sql
and glogin.sql,
respectively. When SQL*Plus first starts, it
simply looks for and executes these scripts in
the working directory or SQL_PATH. That is all
there is to it. Here is a simple example where
SQL*Plus prints “Welcome to SQL*Plus” and lists
both the current database SID and connected user
name. Figure 5.5 shows the login.sql
script and its effect. Notice how the script is
executed automatically upon launch. So in many
cases, one can simply rely upon the login.sql
script behavior rather than writing and then
calling a new script such as
set_environment.sql.
Figure 5.5:
SQL*Plus
using LOGIN.SQL script
As to
the difference between login.sql
and glogin.sql:
SQL*Plus provides two automatic script
environmental setup options. Login.sql
is for a particular user and is loaded from one
of their local directories, whereas glogin.sql
is more for departmental or enterprise wide
standards and usually kept on shared file
servers.
