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 /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.
****************************************************************************

Reply via email to