Can I run host command in select from dual?

[PFC]

Hi All,
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?

[heg]

if you just want to get hostname from database, you can do this:
select machine from v$session where rownum<2;

[kris109]

select host_name from v$instance;

[Shestakov]

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
for example:
select SID,OSUSER,MACHINE from v$session where sid = ( select distinct sid from v$mystat );
...

and then:

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.

[PFC]

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.

TIA!

[kris109]

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.