LLI Wiki — My Digital Knowledge Base

Oracle: customise the SQL*Plus prompt and session with login.sql

Configure SQL*Plus with login.sql or glogin.sql: date format and a prompt showing the user, the database or the date.

The file login.sql (or glogin.sql), placed in $ORACLE_HOME/sqlplus/admin, sets session options and the SQL*Plus prompt automatically at each connection. Inspired by dbaora.com.

Date format and simple prompt

alter session set nls_date_format='YYYY/MM/DD hh:mi:ss';
set sqlprompt "_user'@'_connect_identifier':'_date>  "

With these lines, SQL*Plus displays dates in the chosen format and the prompt shows the user, the connect identifier and the date.

Prompt showing the database name

This block builds the prompt from the global database name (global_name):

set termout off
define new_prompt='nolog'
column value new_value new_prompt

select username || ': ' || substr(substr(global_name,1,30),1,instr(substr(global_name,1,30),'.')-1) value
from user_users, global_name;

set sqlprompt "&new_prompt> "
set termout on

Example of result

export ORACLE_SID=PROD
sqlplus <user>/<password>
PROD> select name from v$database;
PROD
1 Row selected.

PROD>
Mastodon LLIMastodon SocialFosstodon