Re: Load data automatically

> Hi everybody,
> I have a project to do , where I will be required to receive data
 automatically from
> files transmited every hour (every file has a diferent name ). The
 data
> will need to be inserted into the database , and every two days there
 shoul
> d be prepered raport.
>
> What is the best way to do this . What tool should I use ?

I had a similar requirement before on a DEC Alpha running OpenVMS. We received multiple data files every night - the files were generated all day long and placed in an outbound directory. At night, our job would copy the files from the remote box, and then concatenate the similar files together (they had the same extension) At that point, the data was loaded via PRO*Cobol into temporary tables - some quick data checks were done in the cobol. Once out of that, it was into SQL*Plus where we called some pl/sql routines to verify the data and merge it into our production database. When it was all done, a SQL*Plus script generate a report based on an error log table and mailed it back to the user. Additionally, any pending reports were generated.

I'd have to say you could probably do something VERY similar on almost any OS.

1. Setup a job and submit it to run at a specified time.
2. Use sql*loader to load the data into some temporary tables
3. Jump into plus and a pl/sql procedure to load the data and flag problems.
4. Stay in plus and produce a report
5. Resubmit the job for the next hour.

Have an additional job to do the report every 2 days, or determine this during each run based on a last report date flag in the database or something.

Sounds "do-able". Received on Sun Jan 21 2001 - 05:00:10 CET