Need help with Automating Procedure
Hello,
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
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...
'exec schema.procedure_metadata'
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?
Thx!
Ken
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
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...
'exec schema.procedure_metadata'
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?
Thx!
Ken
ASKER
...I'd rather not have to purchase a utility to do this. I thought there was a way to automate these tasks within Oracle itself.
Ken
Ken
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you working on unix .. ?
If yes, I have a script that I use daily and can share with you.
Rgds,
HR
If yes, I have a script that I use daily and can share with you.
Rgds,
HR
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if in the unix (even in the windows), shell script plus sql script seem to have the ability to do thoese steps, don't they?
:)
:)
It can definitely be done in a single shell script in UNIX.
I cannot speak to Windows, that is not my area of expertise, but I would assume it could be done in a script or two in Windows as well.
I cannot speak to Windows, that is not my area of expertise, but I would assume it could be done in a script or two in Windows as well.
johnsone,
second it.
Acton
second it.
Acton
ASKER
Hi all...
Just want to say thanks for all the terrific comments and feedback. Some of the suggestions were a bit over my head. But what I wound up doing was writing a .bat (batch) script in Windows that had an IF ELSE statement that checked for a file in a folder. I'm using Windows 'Task Scheduler' to run the script every 10 min. If the batch script finds the named file in the folder it launches SQLPLUS with database credentials and launches a sqlplus script @script.
Inside the .sql SQLPLUS script I execute the SQLLDR command using the HOST command within SQLPLUS. After that I run the stored procedure. The tasks are then complete.
I know it seems a bit cheesy but it works just fine.
Here's the code that I wrote to make this happen...
Thx again guys...
Ken
BATCH SCRIPT:
IF EXIST "E:\CM MetaData Exports\HOT FOLDER - ImageMetaData\ImageMetaDat
Del "E:\CM MetaData Exports\HOT FOLDER - ImageMetaData\ImageMetaDat
PAUSE
SQLPLUS SCRIPT:
host sqlldr system/password@clientdb control=load_metadata_imag
execute clientschema.pr_load_edito
exit;
Just want to say thanks for all the terrific comments and feedback. Some of the suggestions were a bit over my head. But what I wound up doing was writing a .bat (batch) script in Windows that had an IF ELSE statement that checked for a file in a folder. I'm using Windows 'Task Scheduler' to run the script every 10 min. If the batch script finds the named file in the folder it launches SQLPLUS with database credentials and launches a sqlplus script @script.
Inside the .sql SQLPLUS script I execute the SQLLDR command using the HOST command within SQLPLUS. After that I run the stored procedure. The tasks are then complete.
I know it seems a bit cheesy but it works just fine.
Here's the code that I wrote to make this happen...
Thx again guys...
Ken
BATCH SCRIPT:
IF EXIST "E:\CM MetaData Exports\HOT FOLDER - ImageMetaData\ImageMetaDat
Del "E:\CM MetaData Exports\HOT FOLDER - ImageMetaData\ImageMetaDat
PAUSE
SQLPLUS SCRIPT:
host sqlldr system/password@clientdb control=load_metadata_imag
execute clientschema.pr_load_edito
exit;
http://www.robotask.com/