2010-02-02

SQL Plus profile

When you start SQL Plus or connect, it runs the glogin.sql site profile script and then runs the login.sql user profile script.
The global site profile is $ORACLE_HOME/sqlplus/admin/glogin.sql
The user profile script is login.sql. SQL Plus looks for login.sql in the current directory, and then in the directories specified by the SQLPATH environment variable.
e.g. Following script sets up date format, SQL prompt and shows where you are connected to.

set head off feedback off
ALTER SESSION SET nls_date_format = 'yyyy-mm-dd HH24:MI:SS';
SELECT
'user: '||SYS_CONTEXT('USERENV','CURRENT_USER')||
' inst: '|| SYS_CONTEXT('USERENV','INSTANCE_NAME')||
' db: '|| SYS_CONTEXT('USERENV','DB_NAME')||
' host: '|| SYS_CONTEXT('USERENV','SERVER_HOST') c1
FROM DUAL;
set sqlprompt "_user'@'_connect_identifier> "

set head on feedback on
prompt ___________________________________________________________________
set serveroutput on size 20000 line 200 

Oracle documentation (SQL*Plus® User's Guide and Reference Release 10.2)

1 comment: