utl_file help please

From: Tony Walby <tony.walby_at_bridgewatersystems.com>
Date: Wed, 05 Sep 2001 14:57:05 -0400
Message-ID: <3B967581.1AD5B49E@bridgewatersystems.com>

Here is the procedure it compiles with nor problems. The view session_stats exists and the user has been granted all on it. My quest is when I execute this script with
execute check_jobs_running(1); the file LongJobs.txt gets created but nothing gets written even when I expect the N to be written for v_ans. Any help would be great.

Thanks
Tony

create or replace procedure check_jobs_running(v_magic_number in number)

as

v_ans  char := 'N';
v_what varchar2(1000);
v_pid  varchar2(20);
v_date_dd number(2);
v_date_hh number(2);
v_date_mm number(2);
v_thisdate_dd number(2);
v_thisdate_hh number(2);
v_thisdate_mm number(2);

file_handle UTL_FILE.FILE_TYPE;
cursor long_jobs is select * from session_stats; job_rec long_jobs%rowtype;

begin
  select to_number(to_char(sysdate, 'DD' )),to_number(to_char(sysdate, 'HH24' )),
  to_number(to_char(sysdate, 'MM' )) into v_date_dd,v_date_hh,v_date_mm from dual;
-- Open file to write into and get it's file_handle file_handle :=
UTL_FILE.FOPEN('/u01/app/oracle/oracle_reports/','LongJobs.txt','W'); for job_rec in long_jobs
 loop
  select to_number(to_char(job_rec.logon_time, 'DD' )),   to_number(substr(job_rec.logon_time, 'HH24')), to_number(substr(job_rec.logon_time, 'MM'))   into v_thisdate_dd,v_thisdate_hh,v_thisdate_mm from sys.session_stats;

  if(( v_date_mm > v_thisdate_mm) and ((v_date_hh - v_thisdate_hh) > v_magic_number)) or
  (( v_date_dd > v_thisdate_dd) and (v_thisdate_hh > v_magic_number)) or

  ((v_date_hh - v_thisdate_hh) > v_magic_number)   then
   select what,spid into v_what, v_pid from sys.session_stats where job = job_rec.job;

   v_ans := 'Y';
   UTL_FILE.PUTF (file_handle,'v_ans %s v_pid %s v_what %s');   else
    v_ans := 'N';
    UTL_FILE.PUTF (file_handle,'v_ans %s');   end if;
end loop;
UTL_FILE.FCLOSE(file_handle);
exception

   WHEN NO_DATA_FOUND then
    file_handle :=
UTL_FILE.FOPEN('/u01/app/oracle/oracle_reports/','LongJobs.txt','W');

    v_ans := 'N';
    UTL_FILE.PUTF (file_handle,'v_ans %s');     UTL_FILE.FCLOSE(file_handle);
end;
/ Received on Wed Sep 05 2001 - 13:57:05 CDT