Can I run host command in select from dual?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Can I run host command in select from dual?

  1. #1
    Join Date
    Mar 2001
    Posts
    46
    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?
    ora_newbie@yahoo.com

  2. #2
    Join Date
    Jul 2001
    Posts
    4
    if you just want to get hostname from database, you can do this:
    select machine from v$session where rownum<2;
    Grace

    Oracle DBA/Developer

  3. #3
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    select host_name from v$instance;
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.


  5. #5
    Join Date
    Mar 2001
    Posts
    46
    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!

    ora_newbie@yahoo.com

  6. #6
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    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.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width