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: 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{