CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
Problem in opening file created by SPOOL

 



prabhakaran
New User

Nov 23, 2011, 1:18 PM

Post #1 of 2 (2383 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)


rovf
Veteran

Nov 24, 2011, 7:29 AM

Post #2 of 2 (2363 views)
Re: [prabhakaran] Problem in opening file created by SPOOL [In reply to] Can't Post

You are not creating the file $$.lst anywhere, so it is not surprising that it does not exist.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives