-- login.sql: initialises a SQL*Plus session
-- William Robertson, www.williamrobertson.net

set lines 114
set pages 99
set tab off set trimout on set trimspool on
set term OFF

define mysid=unknown
define myserial=""

var sid number
var serial# number
var tracefile VARCHAR2(200)

col username for a12
col mysid head SID format a6 new_value mysid
col myserial for 999999 new_value myserial
col host_command new_value host_command
col editor new_value _editor
col opid for 999999
col spid for 999999
col host_name for a25
col i_ver head VER for a10
col i_startup_day head STARTED for a8
col segment_name format a30

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RRRR';

BEGIN
	:sid := NVL(:sid,SYS_CONTEXT('userenv','sid'));
END;
/
-- Also need SID as & variable to use in HOST command later:
SELECT TO_CHAR(:sid) AS mysid FROM dual;

SELECT s.username
     , i.instance_name
     , i.host_name
     , ( SELECT SUBSTR(banner, INSTR(banner, 'ease ')+5,10)
         FROM   v$version
         WHERE  rownum = 1 ) i_ver
     , TO_CHAR(startup_time, 'YYYYMMDD') i_startup_day
     , TO_CHAR(s.serial#) AS myserial
     , p.spid, p.pid opid
     , s.saddr
     , p.addr paddr
FROM   v$session s
     , v$instance i
     , v$process p
WHERE  s.paddr = p.addr
AND    sid = SYS_CONTEXT('userenv','sid');

col myserial_str NEW_VALUE myserial_str
SELECT ', serial# &myserial' AS myserial_str FROM dual WHERE '&myserial' IS NOT NULL;

-- Detect OS, use "TITLE" command to set Window display title if Windows, else echo;
SELECT 'echo Connected as: ' AS host_command
     , 'vim' AS editor
FROM   dual
WHERE  SYS_CONTEXT('userenv','TERMINAL') LIKE 'pts/%';

SELECT 'title' AS host_command
     , 'C:\Program Files\Vim\vim71\vim.exe' AS editor
FROM   dual
WHERE  SYS_CONTEXT('userenv','HOST') LIKE '%\%';

host &host_command &_user@&_connect_identifier [session &mysid&myserial_str]
undef myserial_str
undef myserial
undef mysid
undef host_command

col name format a30
col column_name format a30
col name_col_plus_show_param format a30
col VALUE_COL_PLUS_SHOW_PARAM format a80
col LINE/COL format a10
col ERROR format a99

set term off

DECLARE
        v_audsid v$session.audsid%TYPE;
BEGIN
        SELECT sid, serial#, audsid
        INTO   :sid, :serial#, v_audsid
        FROM   v$session
        WHERE  audsid = SYS_CONTEXT('USERENV','SESSIONID')
        AND    status = 'ACTIVE';

        SELECT par.value ||
               DECODE(INSTR(par.value,'/'), 0,CHR(92), '/') ||
               LOWER(th.instance) ||
               '_ora_' || LTRIM(TO_CHAR(TO_NUMBER(pro.spid),'fm99999')) || '.trc' AS filename
        INTO   :tracefile
        FROM   v$process   pro
             , v$session   se
             , v$parameter par
             , v$thread    th
             , v$instance  i
        WHERE  se.audsid = v_audsid
        AND    se.status = 'ACTIVE'
        AND    pro.addr = se.paddr
        AND    par.name = 'user_dump_dest'
        AND    i.instance_name = th.instance;
END;
/

set lines 130
set pages 999
set feed off
set pause >>>
set pause off
set ver off trimspool on
echo off
set serverout on size 1000000 format word tab off
cl buff
set term on

-- 9i upwards:
-- exec DBMS_OUTPUT.PUT_LINE('Session: ' || :sid || CASE WHEN :serial# IS NULL THEN ' (no access to V$ tables)' ELSE ',' || :serial# END)
-- (Not needed in Windows if using "TITLE" command above to display this information in window title)

exec IF :tracefile IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('Tracefile will be: ' || :tracefile); END IF
set feed on feed 1 tab off autoprint on
prompt