Introduction 

The job template SOSSQLPLUSJob provides a standardized and parameterized interface for executing Oracle® SQL*Plus scripts. The job template offers out-of-the-box capabilities

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

Usage

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

When defining the job consider

• to select the JITL job class and
• to specify the com.sos.jitl.jobs.db.oracle.SOSSQLPLUSJob Java class name.

The job arguments can be specified as follows:

Explanation:

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

Arguments

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

Argument: command_script_file

This argument expects a path to the SQL*Plus script that holds the SQL statements to be executed.

Argument: db_url

The db_url argument holds the Oracle service name or instance name.

Argument: db_user

The database user account for which the SQL*Plus script is executed.

Argument: db_password

The password for the database user account specified with the db_user argument.

Argument: shell_command

The value of this variable specifies the location of the SQL*Plus command line utility. The SQL*Plus script will be processed by the indicated SQL*Plus binary.

Argument: variable_parser_reg_expr

This argument specifies a regular expression for parsing the output created by the the DBMS_OUTPUT package from the PL/SQL statements. For each match an order variable is created that is available for subsequent jobs. For example, the statement

EXEC DBMS_OUTPUT.PUT_LINE( 'SET order_date IS ' || TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') );

writes the following output to the console:

SET order_date is 2021-05-04

The output will be parsed by the regular expression

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

that will result in an order variable

order_date = "2021-05-04"

Subsequent jobs can make use of the $order_date variable.

Argument: command_line_options

 The value of this variable specifies the command line parameters required by SQL*Plus, e.g. -V SQL*Plus version , -S silent  no banner , -M automatic HTML output etc.

Argument: ignore_ora_messages

This argument is used to ignore certain Oracle errors and expects a comma separated list of ORA-* error codes that should be ignored.

Argument: ignore_sp2_messages

This argument is used to ignore certain Oracle SP2 errors and expects a comma separated list of SP2* error codes that should be ignored.

Argument: include_files

Should some common SQL code be executed before the execution of the SQL*Plus script then the respective SQL script files can be specified by use of the include_files argument. A number of files can be specified by separating each file name with a semicolon.

Passing Arguments to SQL*Plus Scripts

Job arguments can be passed to the SQL*Plus script.

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.

WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
column end_date new_value BCY_DATE
column period new_value PN_YEAR_PERIOD
column period_prev new_value PN_YEAR_PREV_PERIOD
select '0' as end_date from dual;
prompt SET end_date IS ${BCY_DATE};
/
select '0' as period from dual;
prompt SET period IS ${PN_YEAR_PERIOD};
/
select '0' as period_prev from dual;
prompt SET period_prev IS ${PN_YEAR_PREV_PERIOD};
/
select sysdate from dual;
prompt SET end_date IS 1;
exit;

Passing SQL execution results to subsequent Jobs

JobScheduler jobs can create and update JobScheduler Order parameters. The SOSSQLPlusJob can also pass on the result of SQL script execution i.e. calculated dates, parameters calculated from tables, etc. By default the JobSchedulerSQLPlus job defines a regular expression to parse console output from the execution of SQL Script and sets order parameters for subsequent job steps. For example, the SQL*Plus client stdout  prompt SET period_prev IS ${PN_YEAR_PREV_PERIOD}  displays the output on console; if  period_prev is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ 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 returns the following order variables that are available to subsequent instructions and jobs. 

Return Variable: sql_error

The sql_error variable holds the error messages occurring during SQL*Plus execution. This variable is empty if no errors occur.

Return Variable: std_out_output

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

Return Variable: std_err_output

The std_err_output variable contains the messages spooled to stderr 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 an ORA-* errors occur then the exit code will be set to the value 98.

Further References

• How to work with PL/SQL and the Oracle DBMS
• How to run Oracle Stored Procedures using PL/SQL
• The SOSSQLPlusJob JITL job documentation