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; / -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l