RE: UTL_FILE ISSUE

Ken,

        On thing I can think of is what are the permissions on the
directory?  Are they inherited through a role, if so to run as you
specify they will need to be directly granted. 


Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kenneth Naim
Sent: Monday, May 03, 2010 1:01 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: UTL_FILE ISSUE

I have used utl_file for years without an issue but lately I am running
into
one annoying problem. My data warehouse loads data nightly and on every
first on the month it needs to trigger certain series of about 20
reports to
run on our Business Objects server by writing a file there. So I wrote a
small package that has two procedures one that writes the file and a
second
one that removes it. At the end of the warehouse load the write
procedure is
called, and about 30 minutes later the remove procedure is run. Every
month
the load completes but the file does not get written and the BO reports
do
not run. When I manually run the package/procedure using the same code
the
file gets written and the reports start. 

The only differences are that the nightly batch code is run by the
Oracle
10gr2 scheduler, and that it is call to the write code are from a
procedure
that manages the load. I know the code is being called as I having a
logging
process that execute before and after the call to utl_file.

I am at the point where I am grasping at straws and have tried adding
data
to the file, originally it was blank and last month I added commit for
no
logical reason. 

The database server is physical box running Windows 2003AS 32 bit,
running
Oracle 10.2.0.3 as user Oracle. The business objects server is a virtual
server running windows 2003R2 standard 64 bit. Utlfile is using a Oracle
directory object which contains the full unc path. 

The package I use to write the file is below

Please no switch to *nix style comments, I had no influence on this
environment. 

Thank you,
Ken

CREATE OR REPLACE package POLICY.bo_events
as
    procedure write_event_file;
    procedure remove_event_file (remove_date_in in date);
end;
/

CREATE OR REPLACE package body POLICY.bo_events
as
    v_load_type_id                         loads.load_type_id%type :=15;
    v_load_id                              loads.load_id%type;
    v_process_id                           processes.process_id%type;
    v_number_of_records
processes.number_of_records_processed%type:=0;
    v_process_type_id
process_types.process_type_id%type; 
    v_procedure_name                       varchar2(30);
    v_action                               varchar2(32);
    v_sql                                  varchar2(400);
    v_error_message                        processes.error_message%type;
    v_file_handle                          utl_file.file_type; -- file
handle of os flat file 
    v_file_name                            varchar2(100)
:='start_bo_reports.txt';
    v_directory                            varchar2(100)
:='D_BO_EVENTS';
     
procedure write_event_file
as
    v_text                          varchar2(100)
:=to_char(sysdate,'DD-MON-YYYY HH24:Mi:SS');
begin

    pkg_etl.p_start_load (v_load_type_id,1,null,v_load_id);
   
    v_process_type_id:=304;
    pkg_etl.p_start_process(v_process_type_id, v_load_id, null, null,
null,
v_process_id);
         
        v_file_handle := utl_file.fopen(v_directory, v_file_name,'W');
        utl_file.put_line(v_file_handle, v_text); 
        utl_file.fclose(v_file_handle);
        commit;
    
    pkg_etl.p_end_process(v_process_id, v_number_of_records, null,
false);
    
    pkg_etl.p_end_load (v_load_id, null, false);

exception
   when others
   then pkg_etl.p_end_process(v_process_id, 0, v_error_message, true);
        pkg_etl.p_end_load   (v_load_id, v_error_message, true);
        utl_file.fclose(v_file_handle);
        raise;   
end write_event_file;

procedure remove_event_file (remove_date_in in date)
as
    v_sleep_time                    integer;
begin
    
    v_sleep_time :=round((remove_date_in-sysdate)*24*60*60); 
    pkg_etl.p_start_load (v_load_type_id,1,null,v_load_id);
    
    v_process_type_id:=305;
    pkg_etl.p_start_process(v_process_type_id, v_load_id, null, null,
null,
v_process_id);
        
        if   v_sleep_time>0
        then dbms_lock.sleep(v_sleep_time);
        end if;

        utl_file.fremove(v_directory,v_file_name);
        commit;
              
    pkg_etl.p_end_process(v_process_id, v_number_of_records, null,
false);  
    
    pkg_etl.p_end_load (v_load_id, null, false);   

exception
   when others
   then pkg_etl.p_end_process(v_process_id, 0, v_error_message, true);
        pkg_etl.p_end_load   (v_load_id, v_error_message, true);
        raise;   
end remove_event_file;            
    
end bo_events;
/

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


Other related posts: