Hello Fellow Perlites,

I'm having difficulty parsing a file and could use some help here.
I've attached my code, output file, and sample job run.

Basically, I want to read an Oracle TNS file (tnsnames.ora) and list
the host name and service on that server based on this input file. A
sample TNS entry is multi-lined and looke like this

mysprdtmp (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxr)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysprd01)
)
)

So, when the script runs, I would get this at end of job:

mx01 = tmsprd01
mx16 = mystst04
mxp = mysdev07
mxq = mystst02
mxr = mysprd01
s0869u03 = rdmtst06
ux08 = rdmtst03
uxb = scsdev01

The script generally works fine, but it has a problem when I do not
have a blank line in between these entries. In other words, if my
input file has

mysbse (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysbse)
)
)
oemdev (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = oemdev)
)
)

it should recognize the new TNS listing and give me

mxp = mysbse
mxp = oemdev

(along with the others I am parsing). Again, if I separate the two
with a blank line, it works fine. But since this is a DBA-owned file,
I cannot do this.

My regex looks like

my $tnsEntry = << 'EOF';
^
s*w+s*=s*
s+(DESCRIPTIONs* s+(ADDRESS_LISTs*=s*
s+(ADDRESSs*=s*(PROTOCOLs*=s*TCP)(HOSTs*=s*(w+))(PORTs*=s*d+))
s+)s*
s+(CONNECT_DATAs*=s*
s+(SERVERs*=s*w+)
s+(SERVICE_NAMEs*=s*(w+))
# s+)s*
# s+)
$
EOF


Any help would be appreciated. I've been at this off and on for
several days. Note, this is not a "production" file, but just a
utility script.


Thanks

Jeff


------------------------------------------------------------------------------
perl script follows ("tnsnames.pl"):
------------------------------------------------------------------------------

#!/usr/bin/perl
#
# tnsnames.pl -- reads tnsnames.ora, sorts by host name


use warnings;
use strict;

my $host;
my $name;
my %tns;
my @sortKeys;
my $key;
my $val;
my $para;
my $matchNbr;

$/ = '';

# problem: tns entries with no line break are not distiquished

my $tnsEntry = << 'EOF';
^
s*w+s*=s*
s+(DESCRIPTIONs* s+(ADDRESS_LISTs*=s*
s+(ADDRESSs*=s*(PROTOCOLs*=s*TCP)(HOSTs*=s*(w+))(PORTs*=s*d+))
s+)s*
s+(CONNECT_DATAs*=s*
s+(SERVERs*=s*w+)
s+(SERVICE_NAMEs*=s*(w+))
# s+)s*
# s+)
$
EOF

die "ORACLE_HOME is not defined!n" unless (defined($ENV{ORACLE_HOME} ));

my $tnsFile = "./tnsnames.ora";

open(F, "< $tnsFile") or die "Could not open file $tnsFile: $!n";

while (chomp($para = <F>)) {

undef($host);
undef($name);

if ($para =~ m[$tnsEntry]msxo) {
$matchNbr++;
print "-"x80, "n";
print "*** match #$matchNbr : $paran";
$host = $1;
$name = $2;
print "*** RESULT : host= $host, service= $namen";

if ((defined($host)) && (defined($name))) {
$tns{$host} = $name;
}
}

last if eof(F);
}

close(F);

@sortKeys = sort keys(%tns);

foreach (@sortKeys) {
printf "%-8s = $tns{$_}n", $_;
}


------------------------------------------------------------------------------
sample input file follows ("tnsnames.ora")
------------------------------------------------------------------------------

# TNSNAMES.ORA Network Configuration File:
/u01/app/oracle/product/9.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
#
# history:
# 05/27/04,kk Added rdmdev02 and scsdev01
# 06/01/04,js Added rdmtst02 and rdmtst03 per j.jonawski
# 08/08/04,gv Added mysdev08
# 09/15/04,gv Added mystst04
# 09/20/04,cd Added mysdev04

## mxp ###################################################################
## Reports server
repserver_mysbse=(ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1960))
repserver_mysdev01=(ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1981))
repserver_mysdev02=(ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1982))
repserver_mysdev03=(ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1983))
repserver_mysdev04=(ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1984))
repserver_mysdev05=(ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1985))
repserver_mysdev06=(ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1986))
repserver_mysdev07=(ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1987))
repserver_mysdev08=(ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1988))
repserver_mysdev04=(ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1989))

## mxr ###################################################################
mysprdtmp (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxr)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysprd01)
)
)

mysprd01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxr)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysprd01)
)
)

## mxp ###################################################################
mysbse (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysbse)
)
)
oemdev (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = oemdev)
)
)

mysdev01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev01)
)
)

mysdev02 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev02)
)
)

mysdev03 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev03)
)
)

mysdev04 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SID = mysdev04)
)
)

mysdev05 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev05)
)
)

mysdev06 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev06)
)
)

mysdev07 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev07)
)
)
mysdev08 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev08)
)
)
mysdev04 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev04)
)
)
## mxq ###################################################################
mystst01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxq)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mystst01)
)
)

mystst02 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxq)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mystst02)
)
)

mystst03 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxs)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mystst03)
)
)

## mx16 ###################################################################
mystst04 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mx16)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mystst04)
)
)

## s0867u03 ###################################################################

rdmdev07 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = s0869u03)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmdev07)
)
)

## s0869u03 ###################################################################

rdmtst06 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = s0869u03)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmtst06)
)
)

## ux08 ###################################################################

rdmdev01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = ux08)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmdev01)
)
)

rdmdev02 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = ux08)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmdev02)
)
)

rdmtst02 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = ux08)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmtst02)
)
)

rdmtst03 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = ux08)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmtst03)
)
)

## uxb ###################################################################
rxdev (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = uxb)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rxdev)
)
)

rdmtrn03 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = uxb)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmtrn03)
)
)

scsdev01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = uxb)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = scsdev01)
)
)

## nt-abo-ap1 ############################################################
orcl (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = nt-abo-ap1)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
##########################################################################

INST1_HTTP.LONGS.COM (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

EXTPROC_CONNECTION_DATA (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA (SID = PLSExtProc)
(PRESENTATION = RO)
)
)

# mx01 ##################################################################
tmsprd01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mx01)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = tmsprd01)
)
)


------------------------------------------------------------------------------
sample output file follows ("tnsnames.out")
------------------------------------------------------------------------------

--------------------------------------------------------------------------------
*** match #1 : ## axr
###################################################################
mysprdtmp (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxr)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysprd01)
)
)
*** RESULT : host= mxr, service= mysprd01
--------------------------------------------------------------------------------
*** match #2 : mysprd01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxr)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysprd01)
)
)
*** RESULT : host= mxr, service= mysprd01
--------------------------------------------------------------------------------
*** match #3 : ## axp
###################################################################
mysbse (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysbse)
)
)
oemdev (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = oemdev)
)
)
*** RESULT : host= mxp, service= mysbse
--------------------------------------------------------------------------------
*** match #4 : mysdev01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev01)
)
)
*** RESULT : host= mxp, service= mysdev01
--------------------------------------------------------------------------------
*** match #5 : mysdev02 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev02)
)
)
*** RESULT : host= mxp, service= mysdev02
--------------------------------------------------------------------------------
*** match #6 : mysdev03 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev03)
)
)
*** RESULT : host= mxp, service= mysdev03
--------------------------------------------------------------------------------
*** match #7 : mysdev05 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev05)
)
)
*** RESULT : host= mxp, service= mysdev05
--------------------------------------------------------------------------------
*** match #8 : mysdev06 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev06)
)
)
*** RESULT : host= mxp, service= mysdev06
--------------------------------------------------------------------------------
*** match #9 : mysdev07 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev07)
)
)
mysdev08 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev08)
)
)
mysdev04 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxp)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mysdev04)
)
)
## axq ###################################################################
mystst01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxq)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mystst01)
)
)
*** RESULT : host= mxp, service= mysdev07
--------------------------------------------------------------------------------
*** match #10 : mystst02 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxq)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mystst02)
)
)

mystst03 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mxs)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mystst03)
)
)
*** RESULT : host= mxq, service= mystst02
--------------------------------------------------------------------------------
*** match #11 : ## ax16
###################################################################
mystst04 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mx16)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = mystst04)
)
)
*** RESULT : host= mx16, service= mystst04
--------------------------------------------------------------------------------
*** match #12 : rdmdev07 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = s0869u03)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmdev07)
)
)
*** RESULT : host= s0869u03, service= rdmdev07
--------------------------------------------------------------------------------
*** match #13 : rdmtst06 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = s0869u03)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmtst06)
)
)
*** RESULT : host= s0869u03, service= rdmtst06
--------------------------------------------------------------------------------
*** match #14 : rdmdev01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = ux08)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmdev01)
)
)
*** RESULT : host= ux08, service= rdmdev01
--------------------------------------------------------------------------------
*** match #15 : rdmdev02 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = ux08)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmdev02)
)
)
*** RESULT : host= ux08, service= rdmdev02
--------------------------------------------------------------------------------
*** match #16 : rdmtst02 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = ux08)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmtst02)
)
)
*** RESULT : host= ux08, service= rdmtst02
--------------------------------------------------------------------------------
*** match #17 : rdmtst03 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = ux08)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmtst03)
)
)
*** RESULT : host= ux08, service= rdmtst03
--------------------------------------------------------------------------------
*** match #18 : ## uxb
###################################################################
rxdev (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = uxb)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rxdev)
)
)
*** RESULT : host= uxb, service= rxdev
--------------------------------------------------------------------------------
*** match #19 : rdmtrn03 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = uxb)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = rdmtrn03)
)
)
*** RESULT : host= uxb, service= rdmtrn03
--------------------------------------------------------------------------------
*** match #20 : scsdev01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = uxb)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = scsdev01)
)
)
*** RESULT : host= uxb, service= scsdev01
--------------------------------------------------------------------------------
*** match #21 : # ax01
##################################################################
tmsprd01 (DESCRIPTION (ADDRESS_LIST (ADDRESS = (PROTOCOL = TCP)(HOST = mx01)(PORT = 1521))
)
(CONNECT_DATA (SERVER = DEDICATED)
(SERVICE_NAME = tmsprd01)
)
)
*** RESULT : host= mx01, service= tmsprd01
mx01 = tmsprd01
mx16 = mystst04
mxp = mysdev07
mxq = mystst02
mxr = mysprd01
s0869u03 = rdmtst06
ux08 = rdmtst03
uxb = scsdev01

Jeff Westman wrote:

Jeff Westman wrote: