Re: a database name as sql prompt

From: Robert W. Swisshelm <swisshelm_at_lilly.com>
Date: 1997/06/20
Message-ID: <33AA7294.6882@lilly.com>#1/1

Ronald Hijdra wrote:
>
> Abiy Alemu wrote:
> >
> > Has some one an idea as to how to have the database name as the sql
> > prompt when connected to sql plus ?
> >
> > Thank you!
> >
> > |-----------------------------------------------------------------------------|
> > | If the only tool you have is a | Abiy Alemu abiy_at_biotec.jouy.inra.fr |
> > | hammer, everything in the world | 9, avenue calmels |
> > | seems like a nail.____________________| 92270 Bois colombes (France)________|
>
> This is what you can do:
>
> SQL*plus checks the login.sql or glogin.sql file somewhere in the Oracle
> directory structure. In this file you can select the database name from
> v$database en set the sqlprompt. You can also add the user name from
> sys.dual.
>
> Hope this helps
>
> Ronald Hijdra
> SE/DBA EDS NL
Sometimes you might be really interested in the instance name, rather than the database name. I would imagine that most people keep them the same, but there is usually a case difference in unix. For example, if I create an instance called prod with the database name the same, I will get 'prod' for the instance name, and 'PROD' for the database name. This can cause problems if you are trying to get shell commands to communicate with the database.

Here is a login.sql that sets the prompt to the database name. Note that it requires that as SYS you grant select on v_$database to public.

set termout off
column name noprint new_value prompt
select name from v$database;
set sqlprompt '&&prompt> '
undefine prompt
set termout on
clear column

Here is a login.sql that sets the prompt to the instance name. Note that it requires that as SYS you grant select on v_$thread and v$parameter to public.

set termout off
column instance_name_xxx noprint new_value instance_name select instance instance_name_xxx

     from v$thread t, v$parameter p
     where t.thread# = decode(to_number(p.value),0,1,to_number(p.value))
       and p.name = 'thread';

column instance_name_xxx clear
define instance_name = &instance_name
set sqlprompt '&&instance_name> '
undefine instance_name
set termout on
clear column

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com

Received on Fri Jun 20 1997 - 00:00:00 CDT