I need to make some changes to a few views periodically and need to have the statements run after hours. I have read other posts trying to figure out the best way as the solutions don’t quite fit my needs, or I am not understanding. Please help me understand the proper tool for the job.
I have two or three CREATE OR REPLACE VIEW statements and occasionally a stored procedure or package to update on our production server from a test server. I can create the statements and run them in the editor as a script, but I need schedule them to run after business hours. What is the best method to schedule these types of object updates to run in the evening?
I am relatively current with TOAD for Oracle 12.12.0.39
If you want it to run from the database, you could set up a scheduler job in Schema Browser -> Scheduler Jobs. Choose “SQL_SCRIPT” as the program type, and put the script on the database server, not your local PC.
If you want something to run from your PC, you could set up an “Execute Script” action from Toad’s Automation Designer, and then schedule the action.
There are other ways, but those are a couple that you could set up using Toad.
Thank you for your reply John. I have looked at the scheduler Jobs option and on the Program Info tab I select Specify Program Info. The Program Type list only shows STORED_PROCEDURE, PLSQL_BLOCK, EXECUTABLE or CHAIN. I do not see SQL_SCRIPT. Am I in the correct place? Would I have to set it up as one of these types of scripts in order to run a simple statement?
That list is determined by your version of Oracle. I don’t remember offhand in which version SQL_SCRIPT appeared. I think you could use EXECUTABLE, calling sqlplus as your executable (located on the server), and specifying the login info and script file as parameters.
Given all that, it might be quicker to set up an execute script action. Either one should be possible though.