...

• to execute an SQL*Plus script,
• to pass arguments to an SQL*Plus script,
• to collect results,
• to pass results as Order Variables to the next job as Order Variables.

Usage

The following example shows explains the basic use of the SOSSQLPLUSJob. It executes a simple SQL statement selecting the current system date and displaying writing it to stdout.

When defining the job consider

...

order_date = "2021-05-04"

Subsequent instructions and jobs can make use of the $order_date order variable.

Argument: command_line_options

...

For example assume the following job arguments:

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

...

Passing SQL execution results to subsequent Jobs

...

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

^SETs+(\\s)\\s*ISs(.*)$

This expression matches output that is created with the DBMS_OUTPUT package and expects the output to look like this:

SET my_variable IS some_value

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 for subsequent instructions and jobs and return the period_prev="20140915" order parameter.  All stdout statements matching the ^SETs+(\\s)\\s*ISs(.*)$ regular expression will be set as order_parameters.

Return Variables created by the SOSSQLPLUSJob

The job automatically returns the following order variables Order Variables that are available to subsequent instructions and jobs. 

...

• 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 an ORA-* errors occur then the exit code will be set to the value 98.

...