Hi,
I am not recommending the use of glogin.sql and login.sql to do sql computation. It is degrading the performance and the maintenability of the scripts run over the database.
Myself I am using a big login.sql, which is located in the “SQLPATH” variable. I tested it with 7.3, 8.1.7, 9.2 and 10.0 on AIX. Be aware, it has side effects!
Ok, here it is
host if ! [ -t 0 ] || ! ( unset PERLLIB PERL5LIB; /bin/perl -e ‘use POSIX qw/getpgrp tcgetpgrp/;open(TTY,”/dev/tty”) or exit 1;exit(tcgetpgrp(TTY)!=getpgrp());’ ); then :;else echo @login_ux_fg;fi >/tmp/loginsql.sql
host chmod 2>/dev/null 666 /tmp/loginsql.sql
@/tmp/loginsql
Surprised? Does this looks like sql?
Not really. I am actually checking that sqlplus is not piped (-t 0), and run in terminal (dev/tty), in foreground (getpgrp). Only then I am executing login_ux_fg.sql
Well here is my login_ux_fg.sql, with colors, terminal header and more…
— login_ux_fg.sql
def _editor=vi
set editfile /tmp/tmporacle.sql
set ver off pages 40000 lin 80 long 1000000000 longc 60000 trims on con .
col file_name format a60
col member format a41
col tablespace_name format a20
col db_link format a20
col host format a20
— I am not interrested in that when select * from system views
col ini_trans nopri
col max_trans nopri
col INITIAL_EXTENT nopri
col NEXT_EXTENT nopri
col MAX_EXTENTS nopri
col MIN_EXTENTS nopri
col PCT_INCREASE nopri
col PCT_THRESHOLD nopri
col INCLUDE_COLUMN nopri
col FREELISTS nopri
col FREELIST_GROUPS nopri
col PCT_FREE nopri
— sql error can occurs, if db is down or when no privilege. The show must go on
set termout off echo off arrays 7
whenever sqlerror continue
whenever oserror continue
— save the current line of the query and the query (10g)
host echo > /tmp/lastquery.sql; chmod 2>/dev/null 666 /tmp/lastquery.sql
spool /tmp/lastquerycurrentline.txt
list *
spool off
host chmod 2>/dev/null 666 /tmp/lastquerycurrentline.txt
save /tmp/lastquery.sql rep
host chmod 2>/dev/null 666 /tmp/lastquery.sql
col “_myprompt” new_value myprompt
col “_mytitle” new_value mytitle
col “_user” new_value _USER
col “_privilege” new_value _PRIVILEGE
col “_connect_identifier” new_value _CONNECT_IDENTIFIER
col “_o_release” new_value _O_RELEASE
— default to null
select ‘x’ “_user”, ‘x’ “_privilege”, ‘x’ “_connect_identifier”, ‘x’ “_o_release” from dual where 1=2;
select nvl(‘&_user’, user) “_user”, nvl(‘&_privilege’, decode(user,’SYS’,’AS SYSDBA’,’PUBLIC’,’AS SYSOPER’)) “_privilege”, nvl(‘&_o_release’,’0′) “_o_release” from dual;
select nvl(‘&_connect_identifier’,name) “_connect_identifier” from v$database;
select nvl(‘&_connect_identifier’,substr(global_name, 1, instr(global_name||’.’,’.’))) “_connect_identifier” from global_name;
— check if we support colors / linesize
host if echo $TERM | grep -qsE ‘xterm|dtterm’ ; then echo def _SYSDBA=\”`tput colf1`\”;echo def _NORMAL=\”`tput colf4`\”;echo def _SYSOPER=\”`tput colf5`\”;echo def _RESET=\”`tput sgr0`\”;echo set lin `tput cols`;else echo def _SYSDBA=\”\”;echo def _NORMAL=\”\”;echo def _SYSOPER=\”\”;echo def _RESET=\”\”;echo set lin 80; fi > /tmp/color.sql; chmod 2>/dev/null 666 /tmp/color.sql
@/tmp/color.sql
.
select
decode(‘&_PRIVILEGE’, ‘AS SYSDBA’, ‘&_SYSDBA’, ‘AS SYSOPER’, ‘&_SYSOPER’, ‘&_NORMAL’)||
substr(‘&_USER’||
decode(‘&_CONNECT_IDENTIFIER’, null, null, ‘@&_CONNECT_IDENTIFIER’)||
decode(‘&_PRIVILEGE’, null, null, ‘ &_PRIVILEGE’)||
decode(&_O_RELEASE, null, null,
‘/’||
trunc(&_O_RELEASE/100000000) || ‘.’ ||
mod(trunc(&_O_RELEASE/1000000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/10000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/100),100)
),
1, 33)||
‘&_RESET’||
chr(10)||’SQL> ‘ “_myprompt”
from dual;
select
chr(27)||
‘]2;&_USER’||
decode(‘&_CONNECT_IDENTIFIER’, null, null, ‘@&_CONNECT_IDENTIFIER’)||
rtrim(‘ &_PRIVILEGE’)||
decode(&_O_RELEASE, null, null,
‘/’||
trunc(&_O_RELEASE/100000000) || ‘.’ ||
mod(trunc(&_O_RELEASE/1000000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/10000),100) || ‘.’ ||
mod(trunc(&_O_RELEASE/100),100)
)||
chr(7) “_mytitle”
from dual;
create
.
del 1 last
get /tmp/lastquery
.
host if echo $TERM | grep -qsE ‘xterm|dtterm’ ; then echo;echo ‘&mytitle’;echo; fi
host /usr/bin/sed 1>/tmp/lastquerycurrentline.sql 2>/dev/null -n ‘s/*.*//p’ /tmp/lastquerycurrentline.txt
host chmod 2>/dev/null 666 /tmp/lastquerycurrentline.sql
@/tmp/lastquerycurrentline
col “_myprompt” clear
col “_mytitle” clear
col “_user” clear
col “_privilege” clear
col “_connect_identifier” clear
set sqlp “&myprompt”
undef myprompt mytitle _SYSDBA _SYSOPER _NORMAL _RESET
undef _RC
set arraysize 15
set termout on
Only in sqlplus 10g, this script is run at each connection. It is basically good, because the prompt will be recalculated. What less good is, is that I will have to set whenever error to continue. I found no way to reset it to its original value after connect. Same for termout
so if I have a script
whenever sqlerror exit
connect /
create table …
It will not work as expected. You can search on asktom.oracle.com or on forums.oracle.com, you will find no solution to that problem, but many posts of mines…