
prabhakaran
New User
Nov 23, 2011, 1:18 PM
Post #1 of 2
(3130 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)
|