RE: UTL_FILE ISSUE
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: "'D'Hooge Freek'" <Freek.DHooge@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 3 May 2010 14:45:31 -0400
Interesting idea. I assumed the scheduler ran as the same user as the
database which we had changed to run under the windows user Oracle so it
would have access to the directories on the other server for which granted
access to public as I had some issues initially (a year ago). How can I tell
what user the dbms_scheduler is run as? I'll also start googling/metalinking
it but if there is an empirical way just to be 100% sure.
Thanks,
Ken
-----Original Message-----
From: D'Hooge Freek [mailto:Freek.DHooge@xxxxxxxxx]
Sent: Monday, May 03, 2010 2:03 PM
To: kennethnaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: UTL_FILE ISSUE
Kenneth,
For the moment I don't have access to a windows box to verify, but I thought
that the scheduler service is running as a different user with very limited
privileges.
If so, try to access the directory and to create the file as that user to
verify the privileges are correct.
regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge@xxxxxxxxx
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer: www.uptime.be/disclaimer
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kenneth Naim [kennethnaim@xxxxxxxxx]
Sent: 03 May 2010 19:00
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
Other related posts:
