Re: Automate Load of Multiple csv files

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 Received on Mon Mar 01 2004 - 15:58:20 CST