Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: utl_file help please

Re: utl_file help please

From: Svend Jensen <svend_at_oraclecare.com>
Date: Thu, 06 Sep 2001 21:37:35 +0200
Message-ID: <3B97D07F.A690B715@oraclecare.com>


A quick gues

Permissions on the view session_stats, do they come from a role? Role permissions are not invoked in pl*sql, only direct grants. How about the select grant on the table(s) the view builds upon?

/Svend Jensen

Tony Walby wrote:

> 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 Thu Sep 06 2001 - 14:37:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US