The pkg_etl.p_start_process is an autonomous transaction that I use for error handling and logging. If any of the utl_file lines would raise an error it should go to the exception handler where it would be logged and reraised. Thanks, Ken From: Stefan Knecht [mailto:knecht.stefan@xxxxxxxxx] Sent: Monday, May 03, 2010 1:16 PM To: kennethnaim@xxxxxxxxx Cc: oracle-l-freelists Subject: Re: UTL_FILE ISSUE What I can suggest to further diagnose it, is to instrument your code. Create a little procedure, with pragma autonomous_transaction, and write a line with it to a table after every line of your code. If something unexpected happens, that will help to catch it. Stefan ========================= Stefan P Knecht CEO & Founder s@xxxxxxxx 10046 Consulting GmbH Schwarzackerstrasse 29 CH-8304 Wallisellen Switzerland Phone +41-(0)8400-10046 Cell +41 (0) 79 571 36 27 info@xxxxxxxx http://www.10046.ch ========================= On Mon, May 3, 2010 at 7:00 PM, Kenneth Naim <kennethnaim@xxxxxxxxx> wrote: 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