Need help with Automating Procedure
Posted on 2006-04-03
I'm a bit of a PL/SQL newbie. I'd like to receive some direction on how to automate some tasks that happen against my Oracle Database. Here are the tasks that I currently perform manually.
1) At the command line, I invoke sqlldr to load a text file into a staging table in Oracle. The syntax is as follows...
'sqlldr system/password@dbinstance control=load_metadata.ctl log=metadata.log'
The ctl control file sits in the same folder along with the text file that contains all the data.
2) After I execute this at the command prompt, I then go into SQLPLUS and run the following stored procedure which modifies and moves my staging data into my production table. I log into the dbinstance and then type...
3) The control file that I run in sqlldr in Step 1 looks for the same 'named' text file everytime it is invoked which is ok. I'd like automate this process where every 5 minutes a "Hot Folder" is polled looking for this named text file, processes it thru sqlldr, executes the stored procedure, and then deletes the text file after processing. Then waits another 5 minutes for a another new text file to be dropped into the hot folder.
What's the best way to approach automating all of this? Any thoughts?