Toad World Blog

How to schedule batch jobs in Toad for Oracle Pro DB Admin

Nov 9, 2021 3:00:00 AM by Deepak Vohra

batch jobs in Toad for Oracle

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 in How 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 in How to use Toad® for Oracle with Oracle Autonomous Database.

Toad by Quest-1

 

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 test user
  5. Grant CREATE SESSION, and CREATE TABLE privileges to the test user
  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.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):

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

Selecting Utilities>Script Manager

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.

New Data File

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.

Add scripts

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.

Add scripts

Figure 4. Add Scripts

The Script Manager shows the new batch of scripts (Figure 5) added.

Script Manager lists batch of scripts

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

Run selected scripts

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.

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.

Try Toad for Oracle 

 

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

Run in Toad Script Runner

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

Toad Script Runner

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.

Output from Toad Script Runner

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.

Grid Tabs

Figure 11 Grid Tabs

The History tab (Figure 12) displays the sequence of operations performed.

History tab

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

Execute entire/highlighted script

Figure 13 Execute entire/highlighted script

To configure options, click on Options (Figure 14) in the toolbar.

Options

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.

Combine scripts

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.

Combined script

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

Load in Toad Script Runner

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

Schedule

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.

 

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">

Database>Create>Scheduler>Job

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.

Create Scheduler Job

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.

General settings

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.

Selecting Program Type

Figure 22 Selecting Program Type

In the script field add the scripts (Figure 23) to run as program action.

Configuring Program Scripts

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.

Setting Schedule

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.

Script Output

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 Discussion and 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!

Tags: Toad for Oracle Toad DB Admin Module DBA Edition Oracle Database

Deepak Vohra

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.