I'm not sure if this is a Perl problem, an Oracle problem, a website problem, or a combination of all three. I apologize in advance if it is not a Perl issue.

I'm new to Perl, and have inherited a set of cgi files. The database server was changed and now what used to work no longer works. This is a troubleshooting help enquiry.

I don't expect a solution, but would appreciate if someone could point me in the right (another?) direction. I have read the FAQs and will try to keep this brief and well-formatted.

The tnsnames.ora file has been edited to relect the new server name.

I have not used the -w switch. I know. Burn me, but I have no idea how to retrieve the warnings in code to write them to the log file (below). If you can give me a snippet, in context, I will happily try that and reply.

I know this is immaterial, but I am in a corporate environment and do not have direct access to the servers. If I did, I would be willing to try things on my own, but the situation does not permit that. I have been in several teleconferences and Web-Ex's, but we are not making progress. 

#!/appl/perl-5.6.1/bin/perl

no warnings 'recursion';

use DBI;
use CGI::Carp qw(fatalsToBrowser);
use CGI;
use FileHandle;

$ichs_cfg{shlib_path} = "/usr/oracle_cl8.1.6/lib:/usr/lib:/lib:";
$ichs_cfg{oracle_home} = "/usr/oracle_cl8.1.6";
$ichs_cfg{two_task} = "prch";
$ichs_cfg{tns_admin} = "/home/oracle/tnsnames";

#Next we set some environment variables.
#I don't fully understand the need for some of these either, but it wo
+rks.
$ENV{"LD_RUN_PATH"} ="/usr/oracle_cl8.1.6/lib";
$ENV{"SHLIB_PATH"} = $ichs_cfg{shlib_path};
$ENV{"TWO_TASK"} = $ichs_cfg{two_task};
$ENV{"ORACLE_HOME"} = $ichs_cfg{oracle_home};
$ENV{"TNS_ADMIN"} = $ichs_cfg{tns_admin};
eval "use Oraperl";

$mycgi = CGI->new();

[download]
#Modified by JB54 to include logname parameter (random number) for eac
+h transaction. File locks were becoming a latency issue.
$WWW_tempemp = $mycgi->param("tempemp");
$WWW_logname = $mycgi->param("logname");

$ichs_logs = "/data01/websites/ams/ichs/doc/logs/$WWW_logname.log";

$fh = new FileHandle $ichs_logs, "w";

#defined $fh
print {$fh} "Select PL running.   \n";
print {$fh} "$WWW_tempemp  \n";
print {$fh} "$WWW_logname  \n";

my $curDriver;

my $db = DBI->connect("dbi:Oracle:prch", "User", "PW");

#Added print {fh} and $DBI::errstr\n to die for debugging
($db) || die(print {$fh} "Database did not connect.\n $DBI::errstr\n")
+;

[download]
I have just added commented material above tonight and have not tested it. Is the syntax for the die correct? I really did RTFM, but it was ambiguous to me. The print {$fh} works for the Select PL running and the next two lines. The rest of the code should (and did before the database server change) write the sql select results to a .log file. 
print {$fh} "Database connected...To select users\n";

[download]
$sql = "select * from sysadm.logon_p01 where clock = upper('$WWW_tempe
+mp')";

print {$fh} "$sql....\n";

$sth = $db->prepare($sql) || die "Can't prepare: $DBI::errstr";

$rc = $sth->execute || die "Can't execute: $DBI::errstr";
#Retrieve data from select statement

#Get single row of data returned from oracle.

($clock,$name,$firstname,$middleinitial,$deptid,$phone,$entrydate,
 $removedate,$location,$pageprogress,$course_start_date,$course_comp_d
+ate,
 $final_quiz_complete,$final_quiz_score,$quiz_attempts,$survey_1,$surv
+ey_2,
 $survey_3,$survey_4,$survey5,$survey_6,$survey_7,$survey_8,$survey_9,
+$survey_10,
 $eval_1,$eval_2,$existing_user) = $sth->fetchrow_array;

 print {$fh} "$clock\n$name\n$firstname\n$middleinitial\n$deptid\n$pho
+ne\n$entrydate\n";
 print {$fh} "$removedate\n$location\n$pageprogress\n$course_start_dat
+e\n$course_comp_date\n";
 print {$fh} "$final_quiz_complete\n$final_quiz_score\n$quiz_attempts\
+n$survey_1\n$survey_2\n";
 print {$fh} "$survey_3\n$survey_4\n$survey5\n$survey_6\n$survey_7\n$s
+urvey_8\n$survey_9\n$survey_10\n";
 print {$fh} "$eval_1\n$eval_2\n$existing_user\n";

#Check for problems during fetch
warn $DBI::errstr if $DBI::err;

#Disconnect
$rc  = $db->disconnect || warn $db->errstr;

print {$fh} "disconnecting....\n";
undef $fh;
chmod(0666,$ichs_logs);

#print "Content-Type: text/plain\n\n";
#print "";

exit $status

[download]

In reply to DBI->connect problem by JB54

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.