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;
}
Cannot connect to Oracle db; script will not run
--Beyond some training, I'm very much a newbie to Perl (and this list). So,
please indulge me with my first attempt at a posted question to you; please see
below (*with the full code Perl script toward the bottom of my email)...
-I've been dealing with an issue in a Perl script that I'm writing, similar to
what was posted recently under 'Subject: Script to test connecting to Oracle
DBs' (and 'Subject: Re: Script to test connecting to Oracle DBs' by Rob Dixon).
Specifically, from my code:
$ENV{TWO_TASK} = lady;
$dbh = DBI-connect($connString, $ladyUser, $ladyPass,
{ AutoCommit=0, RaiseError=0, PrintError=0,
ora_check_sql=0 }) or die Could not connect to database: . DBI-errstr ;
... ...but I still keep getting the same error message at command line in unix:
naszcard@gustavo: /ou8/naszcard/sox = perl -c Clapper_jjw_lookups.pl
Clapper_jjw_lookups.pl syntax OK
naszcard@gustavo: /ou8/naszcard/sox = perl Clapper_jjw_lookups.pl -t
Could not connect to database: ORA-24327: need explicit attach before
authenticating a user (DBD ERROR: OCISessionBegin) at Clapper_jjw_lookups.pl
line 134.
FYI: line 134 is above - { AutoCommit=0, RaiseError=0, PrintError=0,
ora_check_sql=0 }) or die Could not connect to database: . DBI-errstr;
-So, after double-checking that I was using the correct SID, and banging my
head against the wall ;-), I commented out the 'use DBI;' and the section
above, and just tried to go straight to it, command line, and do it this way
instead:
my $tempCmd = sqlplus /nolog \@ . '/ou8/naszcard/sox/spool_lookup1b.sql
' . $gustavoUser $gustavoPass | grep ORA;
@oracleErrors = '$tempCmd';
if(@oracleErrors ne 0) {
$tempDate = `date`;
chomp($tempDate);
my $tempORAError = join(/n,@oracleErrors);
system(echo \Clapper_jjw_lookups.pl has failed due to ORACLE
ERROR(S): \n\n It is $tempDate \n\n $tempORAError\ | mailx -s \Moja Table
Lookups Errors\ \ $global_email \ );
exit;
}
~That produces this not very informative, error generated email:
-Original Message-
From: service - PRC [mailto:naszc...@acxiom.com]
Sent: Monday, July 30, 2012 10:51 PM
To: Warren James - jawarr
Subject: Moja Table Lookups Errors
Clapper_jjw_lookups1.pl has failed due to ORACLE ERROR(S):
It is Mon Jul 30 22:50:47 CDT 2012
(@oracleErrors is an array which doesn't seem to be working...)
*Here is the current, full text of my Perl code (passwords and other sensitive,
proprietary, etc., info. changed/hidden/masked to protect the innocent ;-):
#!/usr/bin/perl
use strict;
use warnings;
#use DBI;
#use DBD::Oracle qw(:ora_types);
use Net::SFTP::Foreign;
use diagnostics;
## SCRIPT: Clapper_jjw_lookups.pl
## AUTHOR: James J. Warren -- jawarr
## NOTES: ERRORs logged and emailed
##
## First Perl script for James
##
# --
# Global Declaration --
# --
my $logPath = ; #-- Directory where all logs go (Does include last
'/' )
my $codePath = ; #-- Directory where all scripts are (Does include
last '/' )
my $ftpDirectory = '/outbound/sftpp/xpj123/';
my $global_email = naszprod\@acxiom.com; # -- Global email (All emails
will be mailed to this address)
#my $dbh = ; # -- Database handle that will be initiated later
my $sth = ; # -- Statement handle that will be initiated later
my @oracleErrors;
my $fileConnectionString = ;
my $connString = ;
my $ladyUser = ;
my $ladyPass = ;
my $gustavoUser = ;
my $gustavoPass = ;
my @finishedSqlScripts;
my $tempQuery = ;
my $err_str = ;
# --
# End of Global Declaration ---
# --
my $startupCounter = 0;
our($opt_t);
init();
if ( $opt_t ){
$fileConnectionString = `cat ///xxx/logon.sql`; # -- The string
passed into the code that decides what user and password to use when login into
Oracle
$connString = 'dbi:Oracle:SID';
$logPath = '/xxx//logs/reporting/dev/';
$codePath = '/xxx//xxx/';
$ladyUser = \@lady;
$ladyPass = ;
$gustavoUser = ;
$gustavoPass = ;
$global_email = James.Warren\@acxiom.com;
}
else{
$fileConnectionString = `cat ///xxx/logon.sql`; # -- The string
passed into the code that decides what user and password to use when login into
Oracle
$connString = 'dbi:Oracle:SID'; # --- Used in the DBI creation to connect
to DB
$logPath =
Re: Cannot connect to Oracle db; script will not run
Hi,
I've seen:
$connString = 'dbi:Oracle:SID'
Maybe it helps to add the SID like:
$connString = 'dbi:Oracle:SID=SID_NAME'
where SID_NAME is the SID you want to use...
--Octavian
- Original Message -
From: Warren James - jawarr james.war...@acxiom.com
To: beginners beginn...@perl.org; dbi-users dbi-users@perl.org
Cc: newbie01 perl newbie01.p...@gmail.com; rob.di...@gmx.com
Sent: Wednesday, August 01, 2012 3:54 AM
Subject: Cannot connect to Oracle db; script will not run
--Beyond some training, I'm very much a newbie to Perl (and this list). So,
please indulge me with my first attempt at a posted question to you; please see
below (*with the full code Perl script toward the bottom of my email)...
-I've been dealing with an issue in a Perl script that I'm writing, similar to
what was posted recently under 'Subject: Script to test connecting to Oracle
DBs' (and 'Subject: Re: Script to test connecting to Oracle DBs' by Rob Dixon).
Specifically, from my code:
$ENV{TWO_TASK} = lady;
$dbh = DBI-connect($connString, $ladyUser, $ladyPass,
{ AutoCommit=0, RaiseError=0, PrintError=0,
ora_check_sql=0 }) or die Could not connect to database: . DBI-errstr ;
... ...but I still keep getting the same error message at command line in unix:
naszcard@gustavo: /ou8/naszcard/sox = perl -c Clapper_jjw_lookups.pl
Clapper_jjw_lookups.pl syntax OK
naszcard@gustavo: /ou8/naszcard/sox = perl Clapper_jjw_lookups.pl -t
Could not connect to database: ORA-24327: need explicit attach before
authenticating a user (DBD ERROR: OCISessionBegin) at Clapper_jjw_lookups.pl
line 134.
FYI: line 134 is above - { AutoCommit=0, RaiseError=0, PrintError=0,
ora_check_sql=0 }) or die Could not connect to database: . DBI-errstr;
-So, after double-checking that I was using the correct SID, and banging my
head against the wall ;-), I commented out the 'use DBI;' and the section
above, and just tried to go straight to it, command line, and do it this way
instead:
my $tempCmd = sqlplus /nolog \@ . '/ou8/naszcard/sox/spool_lookup1b.sql
' . $gustavoUser $gustavoPass | grep ORA;
@oracleErrors = '$tempCmd';
if(@oracleErrors ne 0) {
$tempDate = `date`;
chomp($tempDate);
my $tempORAError = join(/n,@oracleErrors);
system(echo \Clapper_jjw_lookups.pl has failed due to ORACLE
ERROR(S): \n\n It is $tempDate \n\n $tempORAError\ | mailx -s \Moja Table
Lookups Errors\ \ $global_email \ );
exit;
}
~That produces this not very informative, error generated email:
-Original Message-
From: service - PRC [mailto:naszc...@acxiom.com]
Sent: Monday, July 30, 2012 10:51 PM
To: Warren James - jawarr
Subject: Moja Table Lookups Errors
Clapper_jjw_lookups1.pl has failed due to ORACLE ERROR(S):
It is Mon Jul 30 22:50:47 CDT 2012
(@oracleErrors is an array which doesn't seem to be working...)
*Here is the current, full text of my Perl code (passwords and other sensitive,
proprietary, etc., info. changed/hidden/masked to protect the innocent ;-):
#!/usr/bin/perl
use strict;
use warnings;
#use DBI;
#use DBD::Oracle qw(:ora_types);
use Net::SFTP::Foreign;
use diagnostics;
## SCRIPT: Clapper_jjw_lookups.pl
## AUTHOR: James J. Warren -- jawarr
## NOTES: ERRORs logged and emailed
##
## First Perl script for James
##
# --
# Global Declaration --
# --
my $logPath = ; #-- Directory where all logs go (Does include last
'/' )
my $codePath = ; #-- Directory where all scripts are (Does include
last '/' )
my $ftpDirectory = '/outbound/sftpp/xpj123/';
my $global_email = naszprod\@acxiom.com; # -- Global email (All emails
will be mailed to this address)
#my $dbh = ; # -- Database handle that will be initiated later
my $sth = ; # -- Statement handle that will be initiated later
my @oracleErrors;
my $fileConnectionString = ;
my $connString = ;
my $ladyUser = ;
my $ladyPass = ;
my $gustavoUser = ;
my $gustavoPass = ;
my @finishedSqlScripts;
my $tempQuery = ;
my $err_str = ;
# --
# End of Global Declaration ---
# --
my $startupCounter = 0;
our($opt_t);
init();
if ( $opt_t ){
$fileConnectionString = `cat ///xxx/logon.sql`; # -- The string
passed into the code that decides what user and password to use when login into
Oracle
$connString = 'dbi:Oracle:SID';
$logPath =
Re: Script to test connecting to Oracle DBs
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. I would use one of the many nagios/icinga plugins for doing so, start here: https://www.itefix.no/i2/check_oracle http://exchange.nagios.org/directory/Plugins/Databases/Oracle regards Jens www.biotronik.com BIOTRONIK SE Co. KG Woermannkehre 1, 12359 Berlin, Germany Sitz der Gesellschaft: Berlin, Registergericht: Berlin HRA 6501 Vertreten durch ihre Komplementärin: BIOTRONIK MT SE Sitz der Gesellschaft: Berlin, Registergericht: Berlin HRB 118866 B Geschäftsführende Direktoren: Christoph Böhmer, Dr. Werner Braun, Dr. Lothar Krings, Dr. Torsten Wolf BIOTRONIK - A global manufacturer of advanced Cardiac Rhythm Management systems and Vascular Intervention devices. Quality, innovation, and reliability define BIOTRONIK and our growing success. We are innovators of technologies like the first wireless remote monitoring system - Home Monitoring®, Closed Loop Stimulation and coveted lead solutions as well as state-of-the-art stents, balloons and guide wires for coronary and peripheral indications. We highly invest in the development of drug eluting devices and are leading the industry with our drug eluting absorbable metal scaffold program. This e-mail and the information it contains including attachments are confidential and meant only for use by the intended recipient(s); disclosure or copying is strictly prohibited. If you are not addressed, but in the possession of this e-mail, please notify the sender immediately and delete the document.
Re: Script to test connecting to Oracle DBs
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
my $user = '';
my $passwd = '';
my $tnsName = '';
$ENV{'ORACLE_HOME'} = 'C:\oracle\product\10.2.0\client_1';
my $dbh = DBI-connect(dbi:Oracle:$tnsName, $user, $passwd);
my $SQL = qq{ SELECT XXX};
my $sth0 = $dbh-prepare($SQL);
print SQL is prepared;\n;
$sth0-execute();
print SQL is executed;\n;
$sth0-finish();
On Fri, Jul 27, 2012 at 6:45 PM, newbie01 perl newbie01.p...@gmail.com 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.
Re: Cannot connect to Oracle db; script will not run
On Jul 31, 2012, at 5:54 PM, Warren James - jawarr wrote:
--Beyond some training, I'm very much a newbie to Perl (and this list). So,
please indulge me with my first attempt at a posted question to you; please
see below (*with the full code Perl script toward the bottom of my email)...
-I've been dealing with an issue in a Perl script that I'm writing, similar
to what was posted recently under 'Subject: Script to test connecting to
Oracle DBs' (and 'Subject: Re: Script to test connecting to Oracle DBs' by
Rob Dixon).
Specifically, from my code:
$ENV{TWO_TASK} = lady;
$dbh = DBI-connect($connString, $ladyUser, $ladyPass,
{ AutoCommit=0, RaiseError=0, PrintError=0,
ora_check_sql=0 }) or die Could not connect to database: . DBI-errstr ;
You may need to change your connection string, depending on how your oracle
client is set up. mine is:
host=oracle.host.dns.name;sid=SID
As I dimly recall we had to make that change at some point in the past to get
dbi working, if you're not using the standard port, you need to put that in
there too.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
RE: Cannot connect to Oracle db; script will not run
have a look at
http://search.cpan.org/~pythian/DBD-Oracle-1.46/lib/DBD/Oracle/Troubleshooting.pm
it should answer most of your questions
From: james.war...@acxiom.com
To: beginn...@perl.org; dbi-users@perl.org
CC: newbie01.p...@gmail.com; rob.di...@gmx.com
Subject: Cannot connect to Oracle db; script will not run
Date: Wed, 1 Aug 2012 00:54:13 +
--Beyond some training, I'm very much a newbie to Perl (and this list). So,
please indulge me with my first attempt at a posted question to you; please
see below (*with the full code Perl script toward the bottom of my email)...
-I've been dealing with an issue in a Perl script that I'm writing, similar
to what was posted recently under 'Subject: Script to test connecting to
Oracle DBs' (and 'Subject: Re: Script to test connecting to Oracle DBs' by
Rob Dixon).
Specifically, from my code:
$ENV{TWO_TASK} = lady;
$dbh = DBI-connect($connString, $ladyUser, $ladyPass,
{ AutoCommit=0, RaiseError=0, PrintError=0, ora_check_sql=0 }) or die
Could not connect to database: . DBI-errstr ;
... ...but I still keep getting the same error message at command line in
unix:
naszcard@gustavo: /ou8/naszcard/sox = perl -c Clapper_jjw_lookups.pl
Clapper_jjw_lookups.pl syntax OK
naszcard@gustavo: /ou8/naszcard/sox = perl Clapper_jjw_lookups.pl -t
Could not connect to database: ORA-24327: need explicit attach before
authenticating a user (DBD ERROR: OCISessionBegin) at Clapper_jjw_lookups.pl
line 134.
FYI: line 134 is above - { AutoCommit=0, RaiseError=0, PrintError=0,
ora_check_sql=0 }) or die Could not connect to database: . DBI-errstr;
-So, after double-checking that I was using the correct SID, and banging my
head against the wall ;-), I commented out the 'use DBI;' and the section
above, and just tried to go straight to it, command line, and do it this way
instead:
my $tempCmd = sqlplus /nolog \@ . '/ou8/naszcard/sox/spool_lookup1b.sql ' .
$gustavoUser $gustavoPass | grep ORA;
@oracleErrors = '$tempCmd';
if(@oracleErrors ne 0) {
$tempDate = `date`;
chomp($tempDate);
my $tempORAError = join(/n,@oracleErrors);
system(echo \Clapper_jjw_lookups.pl has failed due to ORACLE ERROR(S): \n\n
It is $tempDate \n\n $tempORAError\ | mailx -s \Moja Table Lookups Errors\
\ $global_email \ );
exit;
}
~That produces this not very informative, error generated email:
-Original Message-
From: service - PRC [mailto:naszc...@acxiom.com]
Sent: Monday, July 30, 2012 10:51 PM
To: Warren James - jawarr
Subject: Moja Table Lookups Errors
Clapper_jjw_lookups1.pl has failed due to ORACLE ERROR(S):
It is Mon Jul 30 22:50:47 CDT 2012
(@oracleErrors is an array which doesn't seem to be working...)
*Here is the current, full text of my Perl code (passwords and other
sensitive, proprietary, etc., info. changed/hidden/masked to protect the
innocent ;-):
#!/usr/bin/perl
use strict;
use warnings;
#use DBI;
#use DBD::Oracle qw(:ora_types);
use Net::SFTP::Foreign;
use diagnostics;
## SCRIPT: Clapper_jjw_lookups.pl
## AUTHOR: James J. Warren -- jawarr
## NOTES: ERRORs logged and emailed
##
## First Perl script for James
##
# --
# Global Declaration --
# --
my $logPath = ; #-- Directory where all logs go (Does include last '/' )
my $codePath = ; #-- Directory where all scripts are (Does include last
'/' )
my $ftpDirectory = '/outbound/sftpp/xpj123/';
my $global_email = naszprod\@acxiom.com; # -- Global email (All emails
will be mailed to this address)
# my $dbh = ; # -- Database handle that will be initiated later
my $sth = ; # -- Statement handle that will be initiated later
my @oracleErrors;
my $fileConnectionString = ;
my $connString = ;
my $ladyUser = ;
my $ladyPass = ;
my $gustavoUser = ;
my $gustavoPass = ;
my @finishedSqlScripts;
my $tempQuery = ;
my $err_str = ;
# --
# End of Global Declaration ---
# --
my $startupCounter = 0;
our($opt_t);
init();
if ( $opt_t ){
$fileConnectionString = `cat ///xxx/logon.sql`; # -- The string
passed into the code that decides what user and password to use when login
into Oracle
$connString = 'dbi:Oracle:SID';
$logPath = '/xxx//logs/reporting/dev/';
$codePath = '/xxx//xxx/';
$ladyUser = \@lady;
$ladyPass = ;
$gustavoUser = ;
$gustavoPass = ;
$global_email = James.Warren\@acxiom.com;
}
else{
