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:

1. Download and install Toad for Oracle 14 DBA Edition, which includes the DB Admin module, as discussed inHow to download Toad for Oracle.
2. Create an instance of Oracle Autonomous Database and create a connection to the Oracle Database in Toad for Oracle, as discussed inHow to use Toad® for Oracle with Oracle Autonomous Database.

 

When is a batch job needed?

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:

1. Connect to a database instance as user ADMIN
2. Create a new user called test
3. Allocate default tablespace to the test
4. Grant quota on a temporary tablespace to the testuser
5. Grant CREATE SESSION, and CREATE TABLEprivileges to the testuser
6. Connect to the database as the new user test
7. Create a table
8. Add data to the new table
9. Query the table data
10. Drop the table
11. Disconnect from the database as user test
12. Connect to the database again as user ADMIN
13. Drop the user test

To perform the sequence of tasks create a SQL script called script1.sqland 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_highto use script as such):

CONNECT ADMIN/Admin___23@db202105212159_high;

CREATE USER test1 IDENTIFIED BY Admin___23 DEFAULT TABLESPACE DATA QUOTA 10M ON DATA TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, CREATE TABLE TO test1;

CONNECT test1/Admin___23@db202105212159_high;

CREATE TABLE t1 (i INTEGER);

INSERT INTO t1(i) VALUES(1);

SELECT * from t1;

DROP TABLE t1;

DISCONNECT;

CONNECT ADMIN/Admin___23@db202105212159_high;

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:

CONNECT ADMIN/ADW___23@db202105212159_medium;

CREATE USER test2 IDENTIFIED BY ADW___23 DEFAULT TABLESPACE DATA QUOTA 10M ON DATA TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, CREATE TABLE TO test2;

CONNECT test2/ADW___23@db202105212159_medium;

CREATE TABLE t2 (i INTEGER);

INSERT INTO t2(i) VALUES(1);

SELECT * FROM t2;

DROP TABLE t2;

DISCONNECT;

CONNECT ADMIN/ADW___23@db202105212159_medium;

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:

CONNECT ADMIN/ADW___23@db202105212159_low;

CREATE USER test3 IDENTIFIED BY ADW___23 DEFAULT TABLESPACE DATA QUOTA 10M ON DATA TEMPORARY TABLESPACE temp;

GRANT CREATE SESSION, CREATE TABLE TO test3;

CONNECT test3/ADW___23@db202105212159_low;

CREATE TABLE t3 (i INTEGER);

INSERT INTO t3(i) VALUES(1);

SELECT * FROM t3;

DROP TABLE t3;

DISCONNECT;

CONNECT ADMIN/ADW___23@db202105212159_low;

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.

 

Creating a batch script

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.sqlthat 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.

 

Running batch jobs

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

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO test1

Grant complete.

SQL> CONNECT test1/ADW___23@db202105212159_high

Connected as TEST1@DB202105212159_HIGH

SQL> CREATE TABLE t1 (i INTEGER)

Table created.

SQL> INSERT INTO t1(i) VALUES(1)

1 row created.

SQL> SELECT * from t1

        I

———-

         1

1 row selected.

SQL> DROP TABLE t1

Table dropped.

SQL> DISCONNECT

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production

SQL> CONNECT ADMIN/ADW___23@db202105212159_high

Connected as ADMIN@DB202105212159_HIGH

SQL> DROP USER test1 CASCADE

User dropped.

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.

**************************************************************************

Connection: ADMIN@DB202105212159_HIGH

**************************************************************************

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.

 

Using the Toad Script Runner

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 SELECTstatements.

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.

 

Combining scripts

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

 

Scheduling a Task

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 onNew… 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.

 

Creating and running a Scheduler Job

Another feature for running batch jobs in Toad for Oracle is Scheduler Job. Select Database>Create>Scheduler>Job (Figure 19).

Create>Scheduler>Job” v:shapes=”image16.jpg”>

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 SchedulerJob.

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.

 

What’s Next?

1. Explore and Select a Toad product at https://support.quest.com/download-product-select
2. Toad for Oracle download Free Trial Version from https://www.quest.com/register/54618/ 
3. Buy Online or Request Pricing for Toad for Oracle at https://www.quest.com/products/toad-for-oracle/
4. Get Product Support for Toad for Oracle at https://support.quest.com/

Have questions about Toad Developer Tools? Click Start Discussionand this blog topic will be transferred to the Toad World Forums.

 

Related information:

Blog: DB performance: 6 benefits from maintaining database health and stability

Blog: DB performance: 120-point database health check across multiple databases

Blog: Toad for Oracle – DB Health Check

Data Sheet: Toad for Oracle DB Admin Module

 

Help your colleagues

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!