Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Automate Load of Multiple csv files

Re: Automate Load of Multiple csv files

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

April wrote:

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

>>April wrote:
>>>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?
>>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
(replace 'x' with a 'u' to reply)
Received on Mon Mar 01 2004 - 19:27:00 CST

Original text of this message