I'm trying to get the hostname of the machine for non-dba users and place this in the SQL prompt. Here is the script.
--Place USERNAME@HOSTNAME on prompt
column user new_value ouser noprint;
column system new_value osystem noprint;
select user from dual;
--next line does not work
select 'host hostname' system from dual;
--desired outputset sql
set sqlprompt '[&ouser@&osystem]SQL>';
What am I doing wrong?
03-04-2002, 05:09 PM
if you just want to get hostname from database, you can do this:
select machine from v$session where rownum<2;
03-04-2002, 05:22 PM
select host_name from v$instance;
03-04-2002, 06:11 PM
If u want, than each sqlplus user have anything special in command promt u should:
-- use glogin.sql file for setup prompt line
-- use query that can get machine name and OS user name from session
select SID,OSUSER,MACHINE from v$session where sid = ( select distinct sid from v$mystat );
col v_r new_value prt noprint;
select '['||rtrim(OSUSER)||'@'||rtrim(MACHINE)||']SQL>' v_r from v$session where sid = ( select distinct sid from v$mystat );
set sqlprompt &prt
I tried this script, it works.
03-05-2002, 10:49 AM
Thanks for the replies...
Two things though:
1. I'm just trying to learn how to run an OS command and use the output in a variable & or &&. I thought it was possible to do this in a select from dual; is this incorrect?
2. In the case of getting the host_name, we do not have select grants on v$session or v$instance, so these solutions don't work.
03-05-2002, 01:00 PM
In 8i, you can use DBMS_SESSION.set_context to create a context and you can use: