Page History
...
- 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:
Name | Value |
---|---|
BCY_DATE | 28.07.2014 |
PN_YEAR_PERIOD | 2014 |
PN_YEAR_PREV_PERIOD | 2013 |
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 value99
. - If either SP2* or an ORA-* errors occur then the exit code will be set to the value
98
.
...