Oracle Job Scheduler


Disclaimer

Your use of this download is governed by Stonebranch’s Terms of Use, which are available at  Stonebranch Integration Hub - Terms of Use.

Overview

Oracle Scheduler, a feature embedded in Oracle Database, facilitates the automation of a variety of tasks, jobs, and processes within the database environment. Users can schedule and oversee the execution of jobs, programs, and scripts at specified intervals or in response to events. This Universal Task enables users to directly schedule, trigger, monitor, and orchestrate jobs and job chains within the Oracle Scheduler through the Python module cx_Oracle in Universal Controller. Additionally, it allows the establishment of dependencies among different Oracle jobs or job chains, as well as connections with processes in other applications or platforms through UAC workflows. This integration permits Oracle Scheduler jobs to be triggered from UAC, either through time-based or event-based mechanisms.

Version Information

Template NameVersion
CS Oracle Scheduler1.0.0

Refer to Changelog for version history information.

Software Requirements

This integration requires a Universal Agent and a Python runtime to execute the Universal Task.

Software Requirements for Universal Template and Universal Task

Requires Python 3.7.6 or higher. Tested with the Universal Agent bundled Python distribution.

Software Requirements for Universal Agent

Both Windows and Linux agents are supported.

  • Universal Agent for Windows x64 Version 7.1.0.0 and later with Python options installed.
  • Universal Agent for Linux Version 7.1.0.0 and later with Python options installed.
  • Ensure Python Module: cx_Oracle is installed in the agent host and accessible to the UAC execution User.
  • It should be also ensured that Oracle client libraries are installed in agent install host

Software Requirements for Universal Controller

Universal Controller Version 7.1.0.0 and later.

Network and Connectivity Requirements

Oracle Client Library and the python module cx_Oracle is installed in the agent host and configured to connect to the Oracle Database Server.

Key Features

This Universal Task provides the following features:

  • Ability to schedule, trigger & orchestrate the execution of Oracle Scheduler Job / Job chains
  • Monitor the Oracle job / Job Chain execution.
  • Print job logs / execution status post-execution.

Import Universal Template

To use the Universal Template, you first must perform the following steps.

  1. This Universal Task requires the Resolvable Credentials feature. Check that the Resolvable Credentials Permitted system property has been set to true.

  2. To import the Universal Template into your Controller, follow these instructions.

  3. When the files have been imported successfully, refresh the Universal Templates list; the Universal Template will appear on the list.

Modifications of this integration, applied by users or customers, before or after import, might affect the supportability of this integration. For more information refer to Integration Modifications.

Configure Universal Task

For a new Universal Task, create a new task, and enter the required input fields.

Input Fields

FieldInput typeDefault valueTypeDescription
Select a FunctionRequired-Choice

Select the required function

  • Trigger Oracle Job
  • Trigger Oracle Chain
Database NameRequired-TextDatabase name or Oracle service name
ServerRequired-TextOracle server name or the IP address
Oracle CredentialsRequired-TextThe Oracle database user credentials for job execution, The Credentials definition should be as follows.
  • User as "Runtime User".
  • User Password as "Runtime Password".
PortRequired-Text

Port of the Database 

Example: 1521

Connection ModeRequired-Choice

Connection Authorization Mode:

[sysdba | sysasm | sysoper | sysbkp | sysdgd | syskmt | None]

Oracle Job NameRequired-TextSpecify the Oracle scheduler Job Name (For chains as well specify the Job Name) the same should be used in Plsql job/chain
PLSQL Job/Chain execution scriptRequired-ScriptOracle scheduler job execution script & run-time parameters, Job name should be SAME as in Oracle Job name
Job Status check - Poll Interval (seconds)Required10IntegerOracle Job execution Monitor Interval, Default is 10 Seconds
Max Number of Times to PollRequired50IntegerSpecify the Maximum number of times to poll, Default is 50 times
Log LevelRequiredInfoChoiceUniversal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL]

Task Examples

UAC Oracle Scheduler Job Definition 

Oracle Scheduler Pl_SQL Script Definition :

Oracle Job Scheduler execution Logs - Universal Controller.

Oracle Job Chain - UAC definition.

Ensure the Job Name is unique while triggering the Oracle chain

Example - PLSQL Oracle Chain execution script:

Task Output

Exit Codes

The exit codes for this Universal Extension are described below.

Exit CodeStatus Classification CodeStatus Classification DescriptionStatus Description
0SUCCESSSuccessful ExecutionSuccess: Successful Task execution
1FAILEDFailed ExecutionFailed: Oracle job execution Failed
10SUCCESS or FailedSuccess or Failed executionSuccess or Failure : Terminating the Oracle job execution monitor as it has exceeded the maximum polling time

STDOUT and STDERR

STDOUT and STDERR provide additional information to the User. The populated content can be changed in future versions of this extension without notice. Backward compatibility is not guaranteed.

Document References

This document references the following documents.

Document LinkDescription
Universal TemplatesUser documentation for creating, working with and understanding Universal Templates and Integrations.
Universal TasksUser documentation for creating Universal Tasks in the Universal Controller user interface.
CredentialsUser documentation for creating and working with credentials.
Resolvable Credentials Permitted PropertyUser documentation for Resolvable Credentials Permitted Property.


Integration Modifications

Modifications applied by users or customers, before or after import, might affect the supportability of this integration. The following modifications are discouraged to retain the support level as applied for this integration.

  • Python code modifications should not be done.
  • Template Modifications
    • General Section
      • "Name", "Extension", "Variable Prefix", "Icon" should not be changed.
    • Universal Template Details Section
      • "Template Type", "Agent Type", "Send Extension Variables", "Always Cancel on Force Finish" should not be changed.
    • Result Processing Defaults Section
      • Success and Failure Exit codes should not be changed.
      • Success and Failure Output processing should not be changed.
    • Fields Restriction Section
      The setup of the template does not impose any restrictions, However with respect to "Exit Code Processing Fields" section.
      1. Success/Failure exit codes need to be respected.
      2. In principle, as STDERR and STDOUT outputs can change in follow-up releases of this integration, they should not be considered as a reliable source for determining success or failure of a task.

Users and customers are encouraged to report defects, or feature requests at Stonebranch Support Desk.

Changelog

ut-cs-oracle-scheduler-1.0.0 (2024-08-08)

Initial Version