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?
if you just want to get hostname from database, you can do this:
select machine from v$session where rownum<2;
select host_name from v$instance;
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.
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.
In 8i, you can use DBMS_SESSION.set_context to create a context and you can use:
SELECT SYS_CONTEXT (’USERENV’, ’HOST’) FROM DUAL;
to get the host name.