question

BI DWH BALA avatar image
BI DWH BALA asked

File Event based scheduling in oracle?

I am loading a file to a oracle table every day between 8 a.m and 9 a.m. The file may come at any time between 8 a.m to 9 a.m.

As of now, we are manually checking the file availability and then loading the file into oracle table.

My client is asking us to automate this process. My team is wondering if there is any way we can schedule based on file arrival in a specified location.

Do we have such option available in oracle scheduling?

oraclescheduling
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Christian13467 avatar image
Christian13467 answered

There is a job scheduler built into oracle.

If you are on oracle 11.2 there is a file watcher which starts on filesystem events.

EDIT:
The job scheduler is perfect to run database internal tasks doing insert, select, update, reorganisation task or other oracle near stuff. If you can link the file as external table. Poll for the file by selecting from external table.

Using cron has always the disadvantage that you must run the task under a privileged account or hard code passwords into the script. But cron is perfect the run os near tasks. Importing files into the database is the border. IMHO its the reason for oracle to introduce file watcher.

6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's better than the old way. It's not clear, though, how you link a file watcher event to the job event. Have you used it?
0 Likes 0 ·
Not yet. We run on windows and 11gr2 on windows is announced for spring.
0 Likes 0 ·
I am using Oracle 10g.
0 Likes 0 ·
Filewatcher is an oracle 11gr2 new feature, not working in oracle 10g.
0 Likes 0 ·
BI DWH BALA, what OS are you using?
0 Likes 0 ·
I am using Unix.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.