prabhakaran
New User
Nov 23, 2011, 1:18 PM
Post #1 of 2
(2998 views)
|
|
Problem in opening file created by SPOOL
|
Can't Post
|
|
Hi there,
I have a requirement to read a spool file created by a SQL script which was called in the same PERL progam.
Though the SPOOL file is getting created, it is not getting accessed by the File handler. I tested for the existence of the file and it says file is not present.
Copying the entire program.
PERL program
----------------
use IO::File;
use strict;
my %replace = (
ARCHDIR => "$ENV{'ARCHDIR'}",
PARAMDIR => "$ENV{'PARAMDIR'}",
FTPINDIR => "$ENV{'FTPINDIR'}",
FTPOUTDIR => "$ENV{'FTPOUTDIR'}",
LOGDIR => "$ENV{'LOGDIR'}",
SCRIPTDIR => "$ENV{'SCRIPTDIR'}" );
my $regex = join "|" , keys (%replace);
my $regex = qr/$regex/;
my $usr=pack "H*", $ENV{"CDW_USR"};
my $pwd=pack "H*", $ENV{"CDW_PWD"};
open(INFILE,"$replace{PARAMDIR}/watch_file.param") || die ("Cannot open the file watch_file.param");
open(ORA,"| $ENV{'ORACLE_HOME'}/bin/sqlplus -s $usr/$pwd") || die ("cannot connect to sql plus");
while (<INFILE>) {
s/($regex)/$replace{$1}/g;
s/\$//g;
(my $filename,my $sla,my $run_days) = split("~");
my @lof= split(",",$filename);
foreach my $lof_idx (@lof) {
my @files = glob("$lof_idx");
( scalar(@files) > 1 ) && (print "MFP:$lof_idx" . "\n");
my @a_run_days = split(",",$run_days);
foreach my $rd_idx (@a_run_days) {
print ORA "\@test.sql $sla $rd_idx $$ \n";
print "$$.lst \n";
(-r "$$.lst") || print "File Not readable \n";
(-e "$$.lst") || print "File Not exists \n";
open(SQL_LOG,"$$.lst") or die("Cannot open spool file $$.lst-",$!); -- This is the problem.
my @logs = <SQL_LOG> ;
close (SQL_LOG);
my $cnt_logs_HL = grep(/HLCNT/,@logs);
my $cnt_logs_HM = grep(/HMCNT/,@logs);
print "1 HL log count -" . $cnt_logs_HL . "\n";
if ( ( $cnt_logs_HL > 0 || $cnt_logs_HM >0 ) && scalar(@files) == 1 ) {
my @ar_st=stat($files[0]);
(time-$ar_st[9])/60 > 599 && print "OFP:@files";
}
}
};
};
};
close (ORA);
close (INFILE);
SQL file
-------
spool &3;
set pages 0;
set feedback off
set serverout on;
define run_day='&2';
define sla=&1;
DECLARE
d_sla_dt DATE;
BEGIN
IF TO_CHAR(sysdate,'DY') = UPPER('&run_day') THEN
d_sla_dt := TO_DATE(TO_CHAR(sysdate,'mm/dd/yyyy ')||&sla,'mm/dd/yyyy hh24:mi');
IF ((sysdate-d_sla_dt)*24*60) BETWEEN 0 AND 95 THEN
DBMS_OUTPUT.PUT_LINE('HLCNT');
ELSIF ((sysdate-d_sla_dt)*24*60) BETWEEN -1 AND -95 THEN
DBMS_OUTPUT.PUT_LINE('HMCNT');
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE('Hi');
END;
/
exec DBMS_OUTPUT.PUT_LINE('Hello');
SPOOL OFF;
Output
----------
MFP:/home/cdwftpu1/in/test1_*_.txt
16594.lst
File Not readable
File Not exists
Cannot open spool file 16594.lst-No such file or directory at old_ver.pl line 39, <INFILE> line 1.
/tmp/CDW/test>old 4: IF TO_CHAR(sysdate,'DY') = UPPER('&run_day') THEN
new 4: IF TO_CHAR(sysdate,'DY') = UPPER('mon') THEN
old 5: d_sla_dt := TO_DATE(TO_CHAR(sysdate,'mm/dd/yyyy ')||&sla,'mm/dd/yyyy hh24:mi');
new 5: d_sla_dt := TO_DATE(TO_CHAR(sysdate,'mm/dd/yyyy ')||11,'mm/dd/yyyy hh24:mi');
Hi
Hello
Here is the output of the SPOOL file btw
cat 16594.lst
old 4: IF TO_CHAR(sysdate,'DY') = UPPER('&run_day') THEN
new 4: IF TO_CHAR(sysdate,'DY') = UPPER('mon') THEN
old 5: d_sla_dt := TO_DATE(TO_CHAR(sysdate,'mm/dd/yyyy ')||&sla,'mm/dd/yyyy hh24:mi');
new 5: d_sla_dt := TO_DATE(TO_CHAR(sysdate,'mm/dd/yyyy ')||11,'mm/dd/yyyy hh24:mi');
Hi
Hello
(This post was edited by prabhakaran on Nov 23, 2011, 1:22 PM)
|
MAIN
SEARCH
WHO'S 
LOG 