2009-09-07

SQL*Plus configuration

SQL*Plus: Loading your own custom environment automatically on startup

To customize your SQL*Plus environment and have it assign your preferences from one session to the next, you will want to edit one or both of its auto-startup scripts. The way SQL*Plus behaves on startup is:

  1. It searches for the file $ORACLE_HOME/qlplus/admin/glogin.sql and, if found, executes any commands it contains. This "global" login script applies to everyone who executes SQL*Plus from that Oracle home, no matter which directory they start in.

  2. Next, it runs the file login.sql in the current directory, if it exists.[*]

    [*] If it doesn't exist, and you have set the environment variable SQLPATH to one or more colon-delimited directories, SQL*Plus will search through those directories one at a time and execute the first login.sql that it finds. As a rule, I don't use SQLPATH because I am easily confused by this sort of skulking about.

The startup script can contain the same kinds of statements as any other SQL*Plus script: SET commands, SQL statements, column formatting commands, and the like.

Neither file is required to be present. If both files are present, glogin.sql executes, followed by login.sql; in the case of conflicting preferences or variables, the last setting wins.

Here are a few of my favorite login.sql settings:

    REM Number of lines of SELECT statement output before reprinting headers
SET PAGESIZE 999

REM Width of displayed page, expressed in characters
SET LINESIZE 132

REM Enable display of DBMS_OUTPUT messages. Use 1000000 rather than
REM "UNLIMITED" for databases earlier than Oracle Database 10g Release 2
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED

REM Change default to "vi improved" editor
DEFINE _EDITOR = /usr/local/bin/vim

REM Format misc columns commonly retrieved from data dictionary
COLUMN segment_name FORMAT A30 WORD_WRAP
COLUMN object_name FORMAT A30 WORD_WRAP

REM set the prompt (works in SQL*Plus

from Oracle9i Database or later)
SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "

In iSQL*Plus, there is no notion of the current directory, so there is no way to have a personal login.sql file. Only the glogin.sql on the server running iSQL*Plus has any effect.