Nov 9, 2021 3:00:00 AM by Deepak Vohra
In database parlance, batch jobs consist of multiple programs, PL/SQL procedures or SQL scripts run together in a sequence, optionally against multiple databases. An individual job is not much different from a SQL snippet you would run in a SQL Editor or worksheet; it has additional features, such as it can be saved and run as needed, or run based on another event, and scheduled to be run at a specific time, repeatedly if needed. Toad® for Oracle by Quest® provides several built-in features such Script Manager, Toad Script Runner and Scheduler Job for running batch jobs. In this article we’ll discuss creating and running a batch job in Toad for Oracle using each of these features.
Setting the environment
Before running any batch jobs, set up the following prerequisite environment:
While most tasks can be accomplished with a single job running a single script, sometimes it’s necessary to run multiple scripts together as batch jobs. We’ll demonstrate with an example. The connection in Oracle Autonomous Database service provides three databases suffixed _high, _medium, and _low. Suppose that you need to connect to each of these databases and perform the following database tasks in the sequence listed:
To perform the sequence of tasks create a SQL script called script1.sql and save it in directory C:\Users\dvohra\AppData\Roaming\Quest Software\Toad for Oracle\14.0 trial\User Files. The user directory would be different for different users. The SQL script (script1.sql) to run on one database is listed (Replace connection credentials such as password Admin___23, and database name db202105212159_high to use script as such):
CREATE USER test1 IDENTIFIED BY Admin___23 DEFAULT TABLESPACE DATA QUOTA 10M ON DATA TEMPORARY TABLESPACE temp;
GRANT CREATE SESSION, CREATE TABLE TO test1;
CREATE TABLE t1 (i INTEGER);
INSERT INTO t1(i) VALUES(1);
SELECT * from t1;
DROP TABLE t1;
DROP USER test1 CASCADE;
Create a second script (script2.sql) to perform the same sequence of tasks against a second database instance. The second script is listed:
CREATE USER test2 IDENTIFIED BY ADW___23 DEFAULT TABLESPACE DATA QUOTA 10M ON DATA TEMPORARY TABLESPACE temp;
GRANT CREATE SESSION, CREATE TABLE TO test2;
CREATE TABLE t2 (i INTEGER);
INSERT INTO t2(i) VALUES(1);
SELECT * FROM t2;
DROP TABLE t2;
DROP USER test2 CASCADE;
Similarly, create a third script (script3.sql) to perform the same sequence of tasks against a third database instance. The third script is listed:
CREATE USER test3 IDENTIFIED BY ADW___23 DEFAULT TABLESPACE DATA QUOTA 10M ON DATA TEMPORARY TABLESPACE temp;
GRANT CREATE SESSION, CREATE TABLE TO test3;
CREATE TABLE t3 (i INTEGER);
INSERT INTO t3(i) VALUES(1);
SELECT * FROM t3;
DROP TABLE t3;
DROP USER test3 CASCADE;
The only difference between the scripts is the database name, and example user name, and example table name.
Next, we’ll create a batch script from these scripts so we can run a batch job.
To create a runnable PL/SQL batch script select Utilities>Script Manager in Toad for Oracle (Figure 1).
Figure 1. Selecting Utilities>Script Manager
In Script Manager, click on New datafile… (Figure 2). A datafile is a batch script consisting of one or more scripts.
Figure 2. New data file…
A New Script Datafile dialog opens prompting the user to save the new datafile. Specify a file name (datafile1), and click on Save.
Next, a dialog opens prompting the user to open and add scripts to the datafile. Select the three scripts script1.sql, script2.sql, and script3.sql that we created earlier and click on Open.
For an existing datafile, scripts may be added with Add scripts…, which opens the same dialog shown in Figure 3.
Figure 3. Add scripts…
In the Add Scripts dialog (Figure 4) the three scripts are listed. Additional scripts may be added with Add…, and scripts may be removed with Remove…. Specify a Group (Batch1) and click on OK.
Figure 4. Add Scripts
The Script Manager shows the new batch of scripts (Figure 5) added.
Figure 5. Script Manager lists batch of scripts
Next, we’ll run the batch of scripts.
To run the batch job, select the scripts. To select the scripts, click on the first script to highlight it, move the cursor to the last script in the list and click on Ctrl+Left Click. With the three scripts selected click on Run selected scripts (Figure 6).
Figure 6. Run selected scripts
One script after the other will run. A Processing Script… dialog should be displayed while a script is running. The Output tab (Figure 7) lists the output.
Figure 7 Output
The scripts are run in SQL * Plus. The output for one script is listed:
SQL> CONNECT ADMIN/ADW___23@db202105212159_high
Connected as ADMIN@DB202105212159_HIGH
SQL> CREATE USER test1 IDENTIFIED BY ADW___23 DEFAULT TABLESPACE DATA QUOTA 10M ON DATA TEMPORARY TABLESPACE temp
SQL> GRANT CREATE SESSION, CREATE TABLE TO test1
SQL> CONNECT test1/ADW___23@db202105212159_high
Connected as TEST1@DB202105212159_HIGH
SQL> CREATE TABLE t1 (i INTEGER)
SQL> INSERT INTO t1(i) VALUES(1)
1 row created.
SQL> SELECT * from t1
1 row selected.
SQL> DROP TABLE t1
Disconnected from Oracle Database 19c Enterprise Edition Release 126.96.36.199.0 - Production
SQL> CONNECT ADMIN/ADW___23@db202105212159_high
Connected as ADMIN@DB202105212159_HIGH
SQL> DROP USER test1 CASCADE
The Messages tab lists the messages including the number of errors for each script. The messages indicate that three scripts are completed in one connection.
Script: C:\Users\dvohra\AppData\Roaming\Quest Software\Toad for Oracle\14.0 trial\User Files\script1.sql completed. 0 errors.
Script: C:\Users\dvohra\AppData\Roaming\Quest Software\Toad for Oracle\14.0 trial\User Files\script2.sql completed. 0 errors.
Script: C:\Users\dvohra\AppData\Roaming\Quest Software\Toad for Oracle\14.0 trial\User Files\script3.sql completed. 0 errors.
Completed 3 scripts in one connection
Get Toad for Oracle Base Subscription today
Subscription / eStore: buy up to 10 licenses at a time, get auto update, support, announcements/invites to education.
Talk to our professionals: demos, custom solutions, volume discounts.
Not ready to buy? Get Toad for Oracle a 3rd way ... try it free for 30 days.
Toad for Oracle includes Toad Script Runner, a managed tool for running scripts that can be configured as needed. Select the batch of scripts as before, and click on Run in Toad Script Runner (Figure 8).
Figure 8 Run in Toad Script Runner
The Toad Script Runner runs each of the scripts as a separate task. The script being currently run gets highlighted (Figure 9).
Figure 9 Toad Script Runner
A Processing Script… dialog shows which task is currently running. The Output tab (Figure 10) displays the output from all the scripts run.
Figure 10 Output from Toad Script Runner
The Errors tab lists the errors if any. The Grid tabs (Figure 11) list the output from SELECT statements.
Figure 11 Grid Tabs
The History tab (Figure 12) displays the sequence of operations performed.
Figure 12 History tab
The Script Environment Information tab displays the System Variables, User Variables, and Bound Variables. Toad Script Runner is a tool by itself and may be used to manage the running of scripts. To run one or more selected scripts, click on Execute entire/highlighted script (Figure 13).
Figure 13 Execute entire/highlighted script
To configure options, click on Options (Figure 14) in the toolbar.
Figure 14 Options
The Toad Script Runner Options dialog provides options for Scripts, Oracle, Grids, and Import Settings, in addition to General options. Click on Apply to apply any new options. Click on OK to complete options configuration and close the dialog.
As an example, click on Use default to set Exception Logging log file directory, and temporary file directory.
The Scripts tab options include configuring login scripts, script output and miscellaneous script options.
The Oracle options include whether to commit, rollback, or prompt users when closing connections.
The Grids options may be used to configure Grids behavior, column sizing, and display.
We had selected individual scripts to run in SQL*Plus, and Toad Task Runner. Scripts to run may be combined with Combine scripts… button (Figure 15) in Script Manager.
Figure 15 Combine scripts…
In the Save As dialog, provide a file name for the combined script and click on Save.
A dialog will be displayed indicating that the scripts have been combined. A combined script (Figure 16) gets added to the list of scripts in Script Manager.
Figure 16 Combined script
A combined script may be run as individual scripts, whether in SQL*Plus or Toad Script Runner. To load a combined script in SQL Editor, right-click on the script and select Load in Editor. To load a combined script in Toad Script Runner, right-click and select Load in Toad Script Runner (Figure 17).
Figure 17 Load in Toad Script Runner
To schedule a task, select one or more scripts to schedule, and click on Schedule… (Figure 18).
Figure 18 Schedule…
In the Schedule Script Action dialog, specify a task name, and click on Next.
Next, configure the task action, or program. Click on Edit… to modify a task program. In Action Details select the application, or program, with Browse.
As an example, select the tsr.exe application for Toad Script Runner and click on Open.
Specify application parameters if needed and click on OK.
Click on Next in Schedule Script Action.
Next, select the conditions that will cause the task to run. Click on New… to add a new condition.
In Trigger Details configure the task schedule and click on OK.
Click on Next in Schedule Script Action. Then, select the user account to use when running the task. Click on Finish to add a task to schedule.
Another feature for running batch jobs in Toad for Oracle is Scheduler Job. Select Database>Create>Scheduler>Job (Figure 19).
Figure 19 Database>Create>Scheduler>Job
The Create Scheduler Job (Figure 20) window will open. In General settings select the DEFAULT_JOB_CLASS. Select events to raise with the button for Raise Events.
Figure 20 Create Scheduler Job
In Select Events to Raise select the events to raise and click on OK.
Select Logging Level. The General settings are shown in Figure 21.
Figure 21 General settings
Select Programs in the margin and select the program category from the drop-down, whether Predefined or Specified. Select Specified as an example. Select Program Type (Figure 22), such as PL/SQL block, Stored procedure, and SQL script. As an example, select SQL_SCRIPT.
Figure 22 Selecting Program Type
In the script field add the scripts (Figure 23) to run as program action.
Figure 23 Configuring Program Scripts
Select schedule limits such as maximum run duration, maximum number of runs, and maximum number of failures.
Select Stop & Restart options.
Add notifications with Notifications>Add. In Create Notification specify recipient, sender, subject, filter condition, and when to send notification. As an example, adding JOB_FAILED to Event would send notification when a job fails. A new notification is listed in Create Scheduler Job.
In Schedule (Figure 24) select from two options; Predefined, or Specified. If the Specified schedule option is selected, specify Start Date, End Date, and Interval. Auto-assist is available for each of these fields with a right-click.
Figure 24 Setting Schedule
Click on OK.
The scheduler job starts to run when it is scheduled to run, and a Processing Script dialog gets displayed to display status. After the script has run, a Toad for Oracle – Confirm dialog will display. To display output, click on Yes. The Script Output (Figure 25) will display.
Figure 25 Script Output
In this article we discussed creating batch jobs to run a batch of scripts against multiple databases. A batch of scripts may be selected together, or combined, and run in SQL *Plus, or Toad Script Runner. A Script Manager is provided to manage scripts. A Scheduler Job may be run to configure and run a program at a scheduled time, and at scheduled intervals.
The benefits of batch jobs
One of the main benefits of batch jobs is that DBAs and developers can quickly and easily run sequences of scripts to accomplish a series of tasks. Instead of manually running a script, taking those results, and making edits to a different database based on those results, batch jobs offer a solution to simplify running multi-step scripts.
Using a tool that offers batch job scheduling makes a DBA or developer’s job even easier, as jobs that need to periodically occur can be arranged to run at regular intervals, or when select criteria are met.
Have questions about Toad Developer Tools? Click Start Discussion and this blog topic will be transferred to the Toad World Forums.
Data Sheet: Toad for Oracle DB Admin Module
If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!
Written by Deepak Vohra
Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.