RE: UTL_FILE ISSUE
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: "'Andy Klock'" <andy@xxxxxxxxxxxxxxx>
- Date: Mon, 3 May 2010 14:38:38 -0400
I like them when they are just hard enough to solve within a reasonable
amount of time/effort. This has past that point months ago. The value is
trunc(sysdate)+2/24. Hmm I hadn't thought about it equating to zero, but
from the log I see the 2 hour wait to remove the file and code shouldn't
unless it would be a bug.
Ken
From: andyklock@xxxxxxxxx [mailto:andyklock@xxxxxxxxx] On Behalf Of Andy
Klock
Sent: Monday, May 03, 2010 1:13 PM
To: kennethnaim@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: UTL_FILE ISSUE
I love these kinds of puzzles. (mostly when they're not my own)
What is the value of remove_date_in ?
I see you do a sleep for " round((remove_date_in-
sysdate)*24*60*60);"
Could that value be equating to zero?
On Mon, May 3, 2010 at 1: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
Other related posts:
