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: beginners@perl.org; dbi-us...@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 +0000
>
> --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 /xxxx/xxxxxxxx/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/xxxxxxxx/logs/reporting/dev/';
> $codePath = '/xxx/xxxxxxxx/xxx/';
>
> $ladyUser = "xxxx\@lady";
> $ladyPass = "xxxxxxxx";
> $gustavoUser = "xxxxxxxx";
> $gustavoPass = "xxxxxxxx";
>
> $global_email = "James.Warren\@acxiom.com";
>
>
> }
> else{
>
> $fileConnectionString = `cat /xxxx/xxxxxxxx/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 = '/ou8/naszcard/logs/reporting/';
> $codePath = '/ou8/naszcard/sox/';
>
> $ladyUser = "xxxx\@lady";
> $ladyPass = "xxxxxxx";
> $gustavoUser = "xxxxxxx";
> $gustavoPass = "xxxxxxxxxx";
>
> $global_email = " naszprod\@acxiom.com ";
>
> }
>
>
>
> sub init()
> {
> use Getopt::Std;
> my $opt_string = 't';
> getopts( "$opt_string" ) or usage();
> }
>
>
>
> #print " Conn: $fileConnectionString \n";
>
> #print "$fileConnectionString \n";
> chomp( my $LOGIN = $fileConnectionString );
> #$LOGIN = substr($LOGIN, index($LOGIN, "CONNECT "));
> $LOGIN = (split / /,$LOGIN)[1];
> #print "Reformatted:\n$LOGIN \n";
> my $ora_user = (split /\//,$LOGIN)[0];
> my $ora_pass = (split /\//,$LOGIN)[1];
>
>
> #print "connStr : $connString \n";
> #print "$ora_user | $ora_pass \n";
> #$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 ;
>
> # $dbh = DBI->connect("$connString", "$ora_user", "ora_pass",
> # { AutoCommit=>0, RaiseError=>0, PrintError=>0, ora_check_sql=>0 }) or die
> "Could not connect to database: " . DBI->errstr ;
>
> # ------------------------------------------------------------------
> # ---- Done getting everything set up -----------------------------
> # ------------------------------------------------------------------
>
>
> my $tempDate = `date`;
> chomp($tempDate);
>
> system("echo \"The table lookups build is Starting... \n\n It is $tempDate\"
> | mailx -s \"Moja Table Lookups Starting\" \" $global_email \" ");
>
> # ------------------------------------------------------------------
> # ---- Start of where to run sql scripts ---------------------------
> # ------------------------------------------------------------------
>
> my $tempCmd = "sqlplus /nolog \@" . '/ou8/naszcard/sox/lookup1.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;
> }
>
>
> $tempCmd = "sqlplus /nolog \@" . '/ou8/naszcard/sox/lookup2.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;
> }
>
> $tempCmd = "sqlplus /nolog \@" . '/ou8/naszcard/sox/lookup4.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;
> }
>
> $tempCmd ="chmod 755 Acxiom_Cb_Lookup.txt Acxiom_Cc_Lookup.txt
> Acxiom_D_Lookup.txt";
> my $results = system("$tempCmd");
> $results = $results >> 8;
>
> if ($results ne 0) {
> $tempDate = `date`;
> chomp($tempDate);
> system("echo \"Clapper_jjw_lookups.pl has failed during a chmod command \n\n
> It is $tempDate \n\n\" | mailx -s \"Moja Table Lookups Error\" \"
> $global_email \" ");
> }
>
> my $datear = "date '+%Y%m%d'";
> $datear = `$datear`;
> chomp($datear);
> $tempCmd ="cp Acxiom_Cb_Lookup.txt
> /ou8/naszcard/loaded/Acxiom_Cb_Lookup_${datear}.txt";
> $results = system("$tempCmd");
> $results = $results >> 8;
>
> if ($results ne 0){
> $tempDate = `date`;
> chomp($tempDate);
> system("echo \"Clapper_jjw_lookups.pl has failed during a cp command \n\n It
> is $tempDate \n\n\" | mailx -s \"Moja Table Lookups Error\" \" $global_email
> \" ");
> }
>
> $tempCmd ="cp Acxiom_Cc_Lookup.txt
> /ou8/naszcard/loaded/Acxiom_Cc_Lookup_${datear}.txt";
> $results = system("$tempCmd");
> $results = $results >> 8;
>
> if ($results ne 0){
> $tempDate = `date`;
> chomp($tempDate);
> system("echo \"Clapper_jjw_lookups.pl has failed during a cp command \n\n It
> is $tempDate \n\n\" | mailx -s \"Moja Table Lookup Error\" \" $global_email
> \" ");
> }
>
> $tempCmd ="cp Acxiom_D_Lookup.txt
> /ou8/naszcard/loaded/Acxiom_D_Lookup_${datear}.txt";
> $results = system("$tempCmd");
> $results = $results >> 8;
>
> if ($results ne 0) {
> $tempDate = `date`;
> chomp($tempDate);
> system("echo \"Clapper_jjw_lookups.pl has failed during a cp command \n\n It
> is $tempDate \n\n\" | mailx -s \"Moja Table Lookups Error\" \" $global_email
> \" ");
> }
>
> $tempCmd ="cp Acxiom_Cb_Lookup.txt
> /datz_u13/DCHP/ftp_out/Acxiom_Cb_Lookup.txt";
> $results = system("$tempCmd");
> $results = $results >> 8;
>
> if ($results ne 0) {
> $tempDate = `date`;
> chomp($tempDate);
> system("echo \"Clapper_jjw_lookups.pl has failed during a cp command \n\n It
> is $tempDate \n\n\" | mailx -s \"Moja Table Lookup Error\" \" $global_email
> \" ");
> }
>
> $tempCmd ="cp Acxiom_Cc_Lookup.txt
> /datz_u13/DCHP/ftp_out/Acxiom_Cc_Lookup.txt";
> $results = system("$tempCmd");
> $results = $results >> 8;
>
> if ($results ne 0) {
> $tempDate = `date`;
> chomp($tempDate);
> system("echo \"Clapper_jjw_lookups.pl has failed during a cp command \n\n It
> is $tempDate \n\n\" | mailx -s \"Moja Table Lookup Error\" \" $global_email
> \" ");
> }
>
> $tempCmd ="cp Acxiom_D_Lookup.txt /datz_u13/DCHP/ftp_out/Acxiom_D_Lookup.txt";
> $results = system("$tempCmd");
> $results = $results >> 8;
>
> if ($results ne 0) {
> $tempDate = `date`;
> chomp($tempDate);
> system("echo \"Clapper_jjw_lookups.pl has failed during a cp command \n\n It
> is $tempDate \n\n\" | mailx -s \"Moja Table Lookup Error\" \" $global_email
> \" ");
> }
>
> $tempCmd = "sqlplus /nolog \@" . '/ou8/naszcard/sox/lookup5.sql ' .
> "$ladyUser $ladyPass | 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
> in insert commit pgp of files\" \" $global_email \" ");
> exit;
> }
>
> exit;
> #------------------------------------------------------
> #---- End of Program --------------------------------
> #------------------------------------------------------
>
>
> END
>
>
>
> THANKS
> JJW
> James Warren - Acxiom Corporation
> Phone: 501.252.7815 | Cell: 501.690.5464
>
>
> ***************************************************************************
> The information contained in this communication is confidential, is
> intended only for the use of the recipient named above, and may be legally
> privileged.
>
> If the reader of this message is not the intended recipient, you are
> hereby notified that any dissemination, distribution or copying of this
> communication is strictly prohibited.
>
> If you have received this communication in error, please resend this
> communication to the sender and delete the original message or any copy
> of it from your computer system.
>
> Thank You.
> ****************************************************************************
>
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
