5 Replies Latest reply: Jun 20, 2014 2:54 PM by sb92075 RSS

    USERENV HOST returns blank

    00143f12-5ddb-4943-b216-f062f7ea84c1

      I run the query below from a sqlplus client, but the returned host name is blank. The ip address or the transaltion of the ip to a name works fine (2nd and third columns). I connect via TCP/IP. Any idea what's wrong?

       

      select sys_context('USERENV','HOST'), sys_context('USERENV','IP_ADDRESS'), UTL_INADDR.get_host_name('13.42.9.151') from dual;

       

      SYS_CONTEXT('USERENV','HOST')

      --------------------------------------------------------------------------------

      SYS_CONTEXT('USERENV','IP_ADDRESS')

      --------------------------------------------------------------------------------

      UTL_INADDR.GET_HOST_NAME('138.42.99.151')

      --------------------------------------------------------------------------------

       

       

      138.42.99.151

      my_host.com

        • 1. Re: USERENV HOST returns blank
          sb92075

          submit bug report with Oracle

          • 2. Re: USERENV HOST returns blank
            sybrand_b

            I have no idea what is wrong and/or whether you understand what the HOST clause returns.

            According to documentation (which you undoubtedly didn't read) it returns the HOSTNAME of the computer which you connect to Oracle, not the HOSTNAME of the database server.

            I wouldn't see why Oracle would make that available because

            - it doesn't matter where a database is running

            - it is irrelevant to SQL

            - it discloses confidential information, and can result into intrusion on the server

             

            That said, you might be just out on a hacking attempt.

             

            Also if there is any issue in an area like this it is usually version-dependent and you didn't bother to specify your four digit Oracle version and platform.

             

            ------------

            Sybrand Bakker

            Senior Oracle DBA

            • 3. Re: USERENV HOST returns blank
              ddf_dba_ifox

              00143f12-5ddb-4943-b216-f062f7ea84c1 wrote:

               

              I run the query below from a sqlplus client, but the returned host name is blank. The ip address or the transaltion of the ip to a name works fine (2nd and third columns). I connect via TCP/IP. Any idea what's wrong?

               

              select sys_context('USERENV','HOST'), sys_context('USERENV','IP_ADDRESS'), UTL_INADDR.get_host_name('13.42.9.151') from dual;

               

              SYS_CONTEXT('USERENV','HOST')

              --------------------------------------------------------------------------------

              SYS_CONTEXT('USERENV','IP_ADDRESS')

              --------------------------------------------------------------------------------

              UTL_INADDR.GET_HOST_NAME('138.42.99.151')

              --------------------------------------------------------------------------------

               

               

              138.42.99.151

              my_host.com

              Which operating system is the client that you're using installed on?

               

               

              David Fitzjarrell

              • 4. Re: USERENV HOST returns blank
                Mark D Powell

                If you want the database host name you might consider reading it from V$INSTANCE:  select host_name from v$instance;

                - -

                For ease of use and security you could hide the query in a user function under an owner with the following grant from user sys: grant select on v_$instance to x

                - -

                HTH -- Mark D Powell --

                OS - UTL_INADDR.get_host_name(IP Addr) reads the host name file and if you pass IP that is not recognized you get an ORA-29257: host 13.42.9.151 unknown type error.

                 

                 

                 

                 

                Message was edited by: Mark D Powell, add PS on when IP address is used

                • 5. Re: USERENV HOST returns blank
                  sb92075

                  SQL> select machine from v$session where sid=2;

                   

                   

                  MACHINE

                  ----------------------------------------------------------------

                  localhost.localdomain