Unanswered: SQL Scripts OR Pro C Code - Urgent Request
I have an event table that is populated all the 24 hours by an Engine (EG). The objective is to dump these records into an ASCII file where the fields are seperated by a comma.
EG also provides plug in mechanism (stubs) and we fill in with Pro C code to get our work done. This is executed before the records are written on to the event table. Essentially, the EG provides a buffer to the stub with the same data that it would write onto the table.
The other option is to write an SQL script that reads the table and then spool the selected records onto a file. The script will be attached on to a cron job so that it is always running.
We need to write 1000 records per file. The volume of data will be say... 1 million records per day. And we need to capture the data onto the file almost real time.
Can any of you please help me choose the best approach that does not affect the performance of the existing engine - EG. Would appreciate some sample data that calculates the performance.
There could be many ways of doing this. But the first comes to my mind is using UTL_FILE package to create your text file. UTL_FILE package contains procedures to write text files and the syntax is similar to C language.
Invoke your code by creating an AFTER INSERT trigger on your event_table. If you want to limit the size of your file to 1000 records, you might have to add more logic in your code.