Page History
...
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 andcom.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:
...