Re: Automate Load of Multiple csv files

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1078190786.144007_at_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.

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!

April Received on Wed Mar 03 2004 - 09:27:27 CST