April wrote:
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<4045fce1$0$19223$ed9e5944_at_reading.news.pipex.net>...
>
>>"April" <PrivateBenjamin_at_hushmail.com> wrote in message
>>news:54df0379.0403030727.189dcd34_at_posting.google.com...
>>
>>>Hi Daniel,
>>>
>>>I have got to the point where I have the oracle function working and
>>>scheduled as a job. But I have the source files located on the
>>>database server. The next step is to move them to a mapped network
>>>share. I understand that you can use a UNC name in utl_file_dir for
>>>the location of the files, but where do you specify the NT userid and
>>>password that will be required to access the share?
>>>
>>>Some people have been suggesting that I use NT scheduler and create a
>>>batch file that maps the network drive and invokes sqlplus to execute
>>>the stored procedure. Shouldn't dbms_jobs in combination with
>>>utl_file_dir accomplish the same task? (Oracle 7.3.4)
>>>
>>>By the way I will be upgrading to 9i, but may not be able to
>>>coordinate the upgrade with the requirement for a scheduled job. So I
>>>am currently stuck with 7.3.4.
>>>
>>>Thanks alot for your help!
>>
>>
>>Hi
>>
>>UTL_FILE executes in the security context of the user that runs the Oracle
>>software - this will almost certainly be local system and thus will not have
>>network access. I think you should be thinking in terms of an OS scheduled
>>task to copy and optionally rename the files and an oracle scheduled job to
>>load them.
>>
>>There is an article by Norman Dunbar on
>>http://www.jlcomp.demon.co.uk/faq/utl_file.html which covers exactly this
>>issue, but I disagree strongly with the suggestion to change the security
>>context of the Oracle service that it contains (sorry Norm).
>
>
>
>
> Hi,
>
> thanks to everyone for their help!
>
> I took Niall's advice and had an NT job scheduled which ftps the files
> to the database server and then took Daniels advice to use a header
> file listing the files to process (which is created upon the ftp).
> The stored procedure then uses utl_file to read the header file and
> open each listed file, then dbms_sql to execute the DML statements in
> the listed file.
>
> For anyone who may need a process like this, here is the stored
> procedure.
>
> CREATE OR REPLACE procedure test_file_read
> --PURPOSE:to open text files containing DML statements and to execute
> those statements on the server
> --METHOD: use of oracle packages UTL_FILE to read the files and
> DBMS_SQL to run dynamic sql statements
> --files are ftp'd to db server via an NT scheduled job from a network
> share
> is
> v_headerfile utl_file.file_type;
> v_filetoprocess utl_file.file_type;
> v_data varchar2(1022);
> v_dir varchar2 (100);
> v_cursorid number;
> v_filename varchar2(1022);
> v_result integer;
>
> begin
> v_dir := 'D:\Ecms';
> -- open header file with filename list
> v_headerfile := utl_file.fopen(v_dir,'fileheader.txt','r');
> loop
> begin
> --get the filename
> utl_file.get_line(v_headerfile,v_filename);
> -- open file, get content and execute sql from file
> v_filetoprocess := utl_file.fopen(v_dir,v_filename,'r');
> loop
> begin
> utl_file.get_line(v_filetoprocess, v_data);
> v_cursorid := dbms_sql.open_cursor;
> dbms_sql.parse(v_cursorid,v_data,dbms_sql.native);
> v_result := dbms_sql.execute (v_cursorid);
> commit;
> dbms_sql.close_cursor (v_cursorid);
> exception
> when no_data_found then
> utl_file.fclose(v_filetoprocess);
> exit;
> end;
> end loop;
> utl_file.fclose(v_filetoprocess);
> exception
> when no_data_found then
> utl_file.fclose(v_headerfile);
> exit;
> end;
> end loop;
> utl_file.fclose(v_headerfile);
> exception
> when utl_file.invalid_filehandle then
> utl_file.fclose_all;
> raise_application_error ( -20000, 'Invalid_Filehandle trapped' );
> when utl_file.invalid_path then
> utl_file.fclose_all;
> raise_application_error(-20001, 'utl_file.invalid_path');
> when utl_file.invalid_mode then
> utl_file.fclose_all;
> raise_application_error(-20001, 'utl_file.invalid_mode');
> when utl_file.invalid_operation then
> utl_file.fclose_all;
> raise_application_error(-20001, 'utl_file.invalid_operation');
> when utl_file.read_error then
> utl_file.fclose_all;
> raise_application_error(-20001, 'utl_file.read_error');
> when utl_file.write_error then
> utl_file.fclose_all;
> raise_application_error(-20001, 'utl_file.write_error');
> when utl_file.internal_error then
> utl_file.fclose_all;
> raise_application_error(-20001, 'utl_file.internal_error');
> when others then
> utl_file.fclose_all;
> if dbms_sql.is_open (v_cursorid) then
> dbms_sql.close_cursor (v_cursorid);
> end if;
> raise;
> end test_file_read;
> /
Glad it worked for you.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Thu Mar 04 2004 - 16:10:47 CST
