perl array with oracle db name

homer4all has asked for the wisdom of the Perl Monks concerning the following question:

Friends... (Question from newbie, sorry if this is too much in detailed but trying to provide all details to avoid any confusion) I'm working on a script which search for all SID entries from Oracle tnsentry file and use distinct SID list to connect and query database for the result. Oracle tnsentry file has got many duplicates with (SID = DB), so trying to use only distinct SID entry. I can easily connect to single database by providing db name but we have 10 database so it gets bit tricky to read tnsentry file (tnsfile.txt) Search for db alias (SID = db1 , SID = db2 etc) Store each db alias temporary into array Use each alias for db connection in $dbConnect function Mycode try:

#!/usr/bin/perl
use strict;

my $uNamePass = "user/password";
my @dbName;

#Uses username password and dbName to connect databsae
# e.g. user/password@db1

foreach my $item(@dbName) {    
  $dbConnect = &connect($uNamePass."@".$dbName);
  &getRows;
}
   exit($exitStatus);

#------------
sub readFile {

my $fName  = "c:\tnsentry.txt";
my $dbName;

# open file
if ( ! open (DAT, $fName) ) {
     &die;
  }

  # read file by each line
   while(<DAT>) {
      chomp;

  # if line begins with # sign, proceed to next
   next if /^\#/;

  # Next not able to figure out how to read each line and store distin
+ct
  # DB name in an array to use for login in $dbConnect
  # I think I need to sepearte SID with /SID/ and search for string ti
+ll it reaches ')'

}
[download]

Sample: tnsentry.txt

DB1=
  (
  (ADDRESS = (PROTOCOL = TCP))
   ...
   ...
  (CONNECT_DATA = (SID = db1))
  )

DB2=
  (
  (ADDRESS = (PROTOCOL = TCP))
   ...
   ...
  (CONNECT_DATA = (SID = db2))
  )

DB3=
  (
  (ADDRESS = (PROTOCOL = TCP))
   ...
   ...
  (CONNECT_DATA = (SID = db3))
  )

DB_ONE=
  (
  (ADDRESS = (PROTOCOL = TCP))
   ...
   ...
  (CONNECT_DATA = (SID = db1))
  )

DB_TWO=
  (
  (ADDRESS = (PROTOCOL = TCP))
   ...
   ...
  (CONNECT_DATA = (SID = db2 ))
  )
[download]

"SID = db1" or "SID = dbn" can appear multiple times in the file so don't want to connect same database multiple times and run query

Comment on perl array with oracle db name Select or Download Code

Replies are listed 'Best First'.
Re: perl array with oracle db name by wjw (Priest) on May 22, 2014 at 17:35 UTC
Add a `use warnings;` after your use strict. That should help with any oddities caused by your code. Your tnsentry.txt file could be read into a hash of hashes by the looks of it to me. (See perldsc) You might take a look at some of the CPAN modules that read various text formats. Perhaps one of the Config::* modules. That might save you a bit of time having to write a parser for reading that file in. If nothing else, looking at the POD for a couple of them should give you some good ideas of how to write your own if you want or have to. Hope that is helpful... Update:Actually, it looks like DBD::Oracle has the ability to do what you want built right into the module. I have not worked with Oracle, but if your file is like This, the the above module looks like it will do what you want without you having to re-invent the wheel. Again, I have not worked with this, so take this with a grain of salt, or a pound... ...the majority is always wrong, and always the last to know about it... Insanity: Doing the same thing over and over again and expecting different results... wjw	[reply] [d/l]
Re^2: perl array with oracle db name by homer4all (Acolyte) on May 22, 2014 at 19:33 UTC
Thank you for your reply... I'm able to connect database without any problem... my problem is 1. read tnsentry file (tnsfile.txt) 2. Search for db alias (SID = db1 , SID = db2 etc) 3. Store each db alias temporary into array 4. Use each alias for db connection in $dbConnect function I can read the file but extracting distinct db alias and use them for connection is the challenge for me.	[reply]
Re^3: perl array with oracle db name by wjw (Priest) on May 22, 2014 at 20:12 UTC
I did find one more place where someone seems to have been wanting to do what you are also after: Here is the code... and here is the originating article... Hope that fits the bill a bit better... ...the majority is always wrong, and always the last to know about it... Insanity: Doing the same thing over and over again and expecting different results... wjw	[reply]