...

The SQLPLUSJob JITL Job Template provides a standardized interface for executing Oracle® SQL*Plus scripts. The job template offers out-of-the-box capabilities for:

• to execute executing an SQL*Plus script,
• to pass passing arguments to an SQL*Plus script,
• to collect collecting results,
• to pass passing results as Order Variables to next jobs,
• to use using a JS7 - Credential Store.

Usage

When defining the job considereither:

• to invoke the Wizard that is available from the job properties tab in the Configuration view and to select the JITL SQLPLUSJob and respective relevant arguments from the Wizard,

or

• to specify the JITL job class and com.sos.jitl.jobs.db.oracle.SQLPLUSJob Java class name , then and add arguments as explained from in the documentation below documentation.

Example

Download: dbSQLPlusExecution.json

The following example shows the basic use of the SQLPLUSJob. It executes SQL*Plus code from an .sql file and writes output to the stdout channel.

...

• The command_script_file argument points to a file e.g. with file with, for example, the following SQL code:
  • WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
SELECT 'SET business_date IS ' || TO_CHAR(SYSDATE,'dd.mm.yyyy DAY') FROM DUAL;

...

SET order_date is 2021-05-04

The This output will be parsed by the regular expression

...

The SQL*Plus script can include references to job arguments with the syntax ${variable} syntax. Occurrences of variables are substituted before execution of the SQL*Plus script.

...

Passing SQL execution results to subsequent Jobs

The job offers allows use of the variable_parser_reg_expr argument. The argument's default value specifies a regular expression:

...