Introduction 

The job template SQLPLUSJob provides The SQLPLUSJob JITL Job Template provides a standardized and parameterized 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 the next job.

Usage

...

• next jobs,
• using a JS7 - Credential Store.

Usage

When defining the job considereither:

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

or

• specify the JITL job class andto specify the com.sos.jitl.jobs.db.oracle.SQLPLUSJobSQLPLUSJob Java class name and add arguments as explained in the documentation below.

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 job arguments can be specified as follows:

...

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

Arguments

Documentation

The Job Documentation including the full list of arguments can be found under: https://www.sos-berlin.com/doc/JS7-JITL/SQLPLUSJob.xml

The SQLPLUSJob The com.sos.jitl.jobs.db.oracle.SQLPLUSJob class accepts the following arguments:

The SQLPLUSJob can be used with a credential store to hold sensitive arguments. For use of the credential_store_* arguments see JS7 - Use of Credential Store with JITL Jobs.

Argument: command_script_file

...

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:

...

As a result an Order Variable with the name my_variable and the value some_value is created. This pattern can be applied to any number of lines each creating a new Order Variable order variable for subsequent instructions and jobs.

...

The std_out_output variable holds the messages spooled to the stdout channel by SQL*Plus.

Return Variable: std_err_output

The std_err_output variable contains the messages spooled to the stderr channel by SQL*Plus.

Return Variable: exit_code

• The exit_code will be 0 If the SQL*Plus script terminates successfully.
• If std_err_output is not empty then the exit code will be set to the value 99.
• If either SP2* or ORA-* errors occur then the exit code will be set to the value 98.

Further References

Cancellation

The following operations are available:

• The cancel operation is applied to the order, not to the SQLPlusJob. In case of cancellation the job will continue until completion and the order will be put in the failed state.
• The cancel/kill operation is applied to the order and to the job. The SQLPlusJob will be killed and the shell process executing the sqlplus command line utility will be terminated. The database connection will be closed and the DBMS session will be terminated. The order will be put in the failed state.
  • The DBMS decides about rolling back transactions when closing the client session.

Further Resources

• JS7 - JITL PLSQLJob
• JS7 - JITL SQLExecutorJob
• JS7 - JITL Common Variables
• JS7 - Job Resources
• JS7 - Use of Credential Store with JITL Jobs
• How to work with PL/SQL and the Oracle DBMS
• How to run Oracle Stored Procedures using PL/SQL
• The SQLPlusJob JITL job documentation