RE: SQL*Plus Client on Windows

Here's a script that I've used for 10.2 databases.  I call it manually using
@l.  It's for fast logon into development environments; otherwise I'd not
have the logon embedded in the script.  The exception handler is also not
robust.

Read this for better exception handling...
http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html

The format allows for nicely formatted db info cut-n-paste into e-mail.


----------------Begin l.sql ------------------------------------------------
clear buffer
accept 1 prompt '  What db? > '
connect system/hardtoguesspassword42@&1

set sqlprompt "_user'@'_connect_identifier> "

host title &_user@&_connect_identifier

set feedback off

begin 
for x in
   (SELECT 
      INSTANCE_NUMBER                                 INSTANCE_NUMBER
     ,UPPER(INSTANCE_NAME)                            INSTANCE_NAME
     ,HOST_NAME                                       HOST_NAME
     ,VERSION                                         VERSION
     ,TO_CHAR(STARTUP_TIME,'MM/DD/YYYY HH24:MI:SS')   STARTUP_TIME
     ,STATUS                                          STATUS
     ,SUBSTR(PLATFORM_NAME,1,30)                      PLATFORM_NAME
     ,TO_CHAR(RESETLOGS_TIME,'MM/DD/YYYY HH24:MI:SS') RESETLOGS_TIME
     ,CURRENT_SCN                                     CURRENT_SCN
     ,OPEN_MODE                                       OPEN_MODE
     ,LOG_MODE                                        LOG_MODE
     ,(select substr(value,1,30) from v$parameter where name =
'service_names') SERVICE_NAME
     ,sys_context('USERENV','SID')                    SID
    FROM v$instance
    NATURAL JOIN  v$database)
loop
 
dbms_output.put_line('---------------------------------------------------');
  -- dbms_output.put_line('| INST_NUMBER:  '  || x.instance_number);
   dbms_output.put_line('| INST_NAME:    '  || x.instance_name);
   dbms_output.put_line('| SERVICE_NAME  '  || x.service_name);
   dbms_output.put_line('| DB_VERSION:   '  || x.version);
   dbms_output.put_line('| HOST:         '  || x.host_name);
   dbms_output.put_line('| PLATFORM:     '  || x.platform_name);   
   dbms_output.put_line('| STARTUP:      '  || x.startup_time);
   dbms_output.put_line('| STATUS:       '  || x.status);
   --dbms_output.put_line('| RESETLOG:     '  || x.resetlogs_time);
   --dbms_output.put_line('| CURRENT_SCN:  '  || x.current_scn);
   dbms_output.put_line('| OPEN_MODE:    '  || x.open_mode);
   dbms_output.put_line('| LOG_MODE:     '  || x.log_mode);
   dbms_output.put_line('| CURRENT_SID:  '  || x.sid);   
 
dbms_output.put_line('---------------------------------------------------');
end loop;

exception 
   when others then 
      dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
/

set feedback on

----------------End l.sql --------------------------------------------------


- Ted


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tanel Poder
Sent: Sunday, September 09, 2007 12:20 AM
To: Michael.Coll-Barth@xxxxxxxxxxxxxxxxxxx; 'oracle-l'
Subject: RE: SQL*Plus Client on Windows

If you are using the command prompt version of Sqlplus, then you can use the
cmd.exe's TITLE command for that.

Here's my i.sql which I use both for identifying where I'm logged on before
doing any changes to database, plus it is called through login.sql. The
window title change happens on the last line:

---------------------- cut here ----------------------

define mysid=unknown

col username for a12
col i_sid head SID for a6 new_value mysid
col serial# for 999999
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

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.sid)          i_sid, 
        s.serial#, 
        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 = (select sid from v$mystat where rownum = 1);

host title &_user@&_connect_identifier [&mysid]

---------------------- cut here ----------------------

And the output is:

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Sep 9 12:16:15 2007

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


USERNAME     INSTANCE_NAME    HOST_NAME                 VER        STARTED
SID    SERIAL# SPID            OPID SADDR    PADDR
------------ ---------------- ------------------------- ---------- --------
------ ------- ------------ ------- -------- --------
SYS          prod01           WINDOWS01                 10.2.0.3.0 20070907
148       2003 3480              19 343371B4 3425173C

SQL>


--
Regards,
Tanel Poder
http://blog.tanelpoder.com
 

> -----Original Message-----

--
http://www.freelists.org/webpage/oracle-l

Other related posts: