Re: utl_file help please

From: David Fitzjarrell <oratune_at_msn.com>
Date: 6 Sep 2001 08:36:49 -0700
Message-ID: <32d39fb1.0109060736.283a1f8f@posting.google.com>

T. Schwickert<schwickert_at_yahoo.com> wrote in message news:<BnGl7.6178$4z.23882_at_www.newsranger.com>...
> Hi,
>
> On Wed, 05 Sep 2001 14:57:05 -0400, a person called Tony Walby thought ...
> >
> >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
> >
> rest deleted...
>
> Just a thought ...
> Be sure, that parameter utl_file_dir is set...
>
> Hth
> Thomas

There are several problems with this procedure; a number of them will be corrected by providing an argument for the '%s' format specifier in the UTL_FILE.PUTF() calls. Another problem is you are NEVER getting to the EXCEPTION section of the code since the loop is terminating successfuly even though there is no data found. The file is being opened and closed without a single character being written to it (therefore proving that the utl_file_dir parameter IS set). Adding a job counter, if you will, to increment for each record retrieved from the view will provide an avenue to output your default message to the file, namely 'v_ans N' since you can now code an if-then statement to check for a job counter equal to 0. My modified code is shown below:

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);
	job_ctr	number:=0;  -- Added to trigger default output
	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('c:\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;

		job_ctr := job_ctr + 1;  -- Increment job counter 
		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', v_ans,
v_pid, v_what);
		else
			v_ans := 'N';
			UTL_FILE.PUTF (file_handle,'v_ans %s', v_ans);
		end if;
	end loop;
	if job_ctr = 0 then
		v_ans := 'N';
		UTL_FILE.PUTF (file_handle,'v_ans %s', v_ans);
	end if;
	UTL_FILE.FCLOSE(file_handle);
exception
	WHEN NO_DATA_FOUND then
		file_handle :=
		UTL_FILE.FOPEN('c:\oracle\oracle_reports\','LongJobs.txt','W');
	 	v_ans := 'N';
		UTL_FILE.PUTF (file_handle,'v_ans %s', v_ans);
		UTL_FILE.FCLOSE(file_handle);

end;
/

The output:

v_ans N

I did not alter the original 'logic' and kept the exception section. I have merely added the job counter and the check for its equality to 0. I also altered the file name (as I am currently running on Win2000, not UNIX or Linux).

David Fitzjarrell
Oracle Certified DBA Received on Thu Sep 06 2001 - 10:36:49 CDT