SPUG: DBI question: Lookup given a SID

[Eric.D.Peterson at alltel.com]

Got it.  TNSPING will tell me.  Now to do a regex or string manipulation
and capture the hostname from the line returned.  While it's not a
"perlish" solution, TNSPING is available both on the PC and Unix boxes I
work on.  I'm using the switch for now as I'm not sure about all the
return permutations.  If it's just these two, then a simple if statement
would be better.

Once I have the hostname in a variable, I can use gethostbyname to get
more information.



#!perl 
# ========== ========== ========== ========== ========== ==========
==========
$|++; # Disable buffering 
use warnings;
use strict;
use Switch;

my $results = `tnsping dbname` or die "Unable to call tnsping\n";
my @array = split ( /\n/, $results ); # breakup the output by line
foreach ( @array )
{
    my $line  = $_;
    chomp $line;
    switch ( $line ) 
    {
        # Expecting something like this when successful
        #
        # Attempting to contact
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1
521)))(CONNECT_DATA=(SID=dbname)))
        case /HOST/ { print "$line\n"; }

        # TNS-03505: Failed to resolve name
        # or any other TNS error
        case /TNS-/ { print "$line\n"; }
    }
} 




-----Original Message-----
Sent: Monday, 08 January, 2007 16:19
To: Peterson, Eric D
Subject: Re: SPUG: DBI question: Lookup given a SID

gethostbyname what you're looking for?

On 1/8/07, Eric.D.Peterson at alltel.com <Eric.D.Peterson at alltel.com>
wrote:
>
>   Given a database name (i.e., SID) is there a way to lookup the 
> hostname and/or ip address?  For example I know the following:
>
>                 db:     dbname
>                 Host:   xxx.alltel.com
>                 IP:     nn.nn.nn.nn
>
>   I'd like to do something like this in Perl.
>
>                 my ( $host, $IP ) = who ( $db );
>
>  In this subroutine I'd do the lookup to get the hostname and IP 
> address.  Do ya'll know of Perl module that will do that?  Or am I 
> just dreaming?  I'd like this to work both on Win32 and Solaris Unix.
>
>  I did some searching, but I must be looking in the wrong places as I 
> can't seem to find much in this area.  The issue is that my company no

> longer has explicit listings in the TNSNAMES.ORA file.  So I just 
> create a simple connection string (my $conn = "dbi:Oracle:$sid";).  So

> essentially all I know is the name of the database.  But in the case 
> of some sort of failure or error, I'd like to print out more then just

> the SID name.  I'd like to say this database on this machine had a 
> problem with your query.
>
> Thanks for any help you can provide.
> Eric
>
>
> **********************************************************************
> ******************** The information contained in this message, 
> including attachments, may contain privileged or confidential 
> information that is intended to be delivered only to the person 
> identified above. If you are not the intended recipient, or the person

> responsible for delivering this message to the intended recipient, 
> Alltel requests that you immediately notify the sender and asks that 
> you do not read the message or its attachments, and that you delete
them without copying or sending them to anyone else.
>
>
> _____________________________________________________________
> Seattle Perl Users Group Mailing List
>      POST TO: spug-list at pm.org
> SUBSCRIPTION: http://mail.pm.org/mailman/listinfo/spug-list
>     MEETINGS: 3rd Tuesdays
>     WEB PAGE: http://seattleperl.org/
>



******************************************************************************************
The information contained in this message, including attachments, may contain 
privileged or confidential information that is intended to be delivered only to the 
person identified above. If you are not the intended recipient, or the person 
responsible for delivering this message to the intended recipient, Alltel requests 
that you immediately notify the sender and asks that you do not read the message or its 
attachments, and that you delete them without copying or sending them to anyone else.