Re: Automate Load of Multiple csv files

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 01 Mar 2004 17:27:00 -0800
Message-ID: <1078190786.144007@yasure>

April wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1077906458.293779_at_yasure>...
>

>>April wrote:
>>
>>
>>>Hi,
>>>
>>>I have been looking into the automated and scheduled import of csv
>>>files (or files containing insert statements) into Oracle 7.3.  I see
>>>that UTL_FILE is used to open and read files.  However, the file name
>>>is required.  I would have multiple files sitting on a network share
>>>of which all would have to be imported and then the processed file
>>>either written to as processed or moved to another location.  Is there
>>>some way to read file names using PL/SQL so that I could pass in the
>>>file name to UTL_FILE.FOPEN? Also this would have to be a scheduled
>>>job to occur regularly?
>>>
>>>Any ideas?
>>>Thanks,
>>>April
>>
>>Send a "header" file with a known file name that contains the names
>>of files to be imported. Use DBMS_JOB to schedule reading the header
>>file and running the UTL_FILE imports.

>
>
>
> Thanks Daniel, that's a great idea.
> Although I think this won't work in my case because the files will be
> deposited on the network share by an app used locally by various
> users. I would still have to poll the directory to see what files are
> there.
>
> Or maybe the concept does work...
> I suppose I could have a central "header" file as you mention, that
> the apps write the filename to when they create the file. The function
> reads the list as you describe, and processes the files. Then I could
> clear out the header file and the function could either delete or move
> the file to a different location. (can Oracle 7.3.4 do this?) I am
> just a little leery of having one file that is written to by many
> different users, read/write conflicts etc.
>
> This is probably exactly what you were saying anyways!
>
> Thanks,
> April

To rename or delete files with UTL_FILE you need 9i. With 7.3.4 you will need Pro*C.

-- 
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 Mon Mar 01 2004 - 19:27:00 CST