Re: Script to test connecting to Oracle DBs

On 28/07/2012 02:45, newbie01 perl wrote:
> Hi all,
> 
> I am looking for a Perl script or something similar that I can use to test
> connection from a client PC to several databases on a server.
> 
> Does anyone know of any such script lying around somewhere :(-
> 
> Currently, am testing connection from a client PC to an Oracle DB using
> Oracle's sqlplus. All is well and good until I have to test connection to
> multiple databases on the server.
> 
> So I am hoping to use a Perl script that I can use which will parse some
> sort of config file that contains hostname.tns_alias combination and then
> test connection to the Oracle DB and run a simple SQL like SELECT COUNT(1)
> from USER_TABLES, if I get an error, that means there is an issue
> connection to that database, otherwise all is good.
> 
> At the moment, easiest I can think of using Perl's system command to run
> sqlplus. Or is it better to look at using DBI? Would be very much
> appreciated if someone can provide me a simple DBI script to start with if
> that is the best approach to use.
> 
> Any suggestion/advice much appreciated. Thanks in advance.

I'm not clear why sqlplus won't let you connect to multiple databases,
assuming you don't want to connect to them all /simultaneously/?

I would say you were better off using the DBI module, and you will also
need the SBS::Oracle driver module. The documentation

    http://metacpan.org/module/DBI
    http://metacpan.org/module/DBD::Oracle

is very extensive, but the basic idea looks like the program below.

I hope this helps.

Rob


use strict;
use warnings;

use DBI;

autoflush STDOUT;

my ($user, $pass) = qw/ username  password /;

my @databases = qw/ db1 db2 db3 db4 /;

for my $dbname (@databases) {

  print "Database: $dbname\n";

  my $dbh = DBI->connect("dbi:Oracle:$dbname", $user, $pass,
      { PrintError => 1, PrintWarn => 1 } );
          
  next unless $dbh;

  my ($count) = $dbh->selectrow_array('SELECT COUNT(*) FROM user_tables');
  print "Database $dbname: Count: $count\n";
}

-- 
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
http://learn.perl.org/