Re: UTL_FILE ISSUE
- From: Andy Klock <andy@xxxxxxxxxxxxxxx>
- To: kennethnaim@xxxxxxxxx
- Date: Mon, 3 May 2010 13:13:16 -0400
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:
