Re: Automate Load of Multiple csv files

"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).

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK