Introduction 

The SOSSQLPlusJob JITL job provides template SOSSQLPLUSJob provides a standardized and parameterized interface for executing Oracle® SQL*Plus scripts.  The JobScheduler The job template offers out-of-the-box capability 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 a the basic example use of the SOSSQLPlusJob SOSSQLPLUSJob. It executes a simple SQL Statment  - statement selecting the current system date and displaying it on to stdout as order_date.

...

language	xml
title	Simple JobSchedulerSQLPlusJob
linenumbers	true
collapse	true

...

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.

Image Added

The job arguments can be specified as follows:

Image Added

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:

Parameters

The SOSSQLPlusJob requires the following parameters:

Argument: command_script_file

• The SQL script  can be:
  

  • saved to a separate file such as get_order.sql . This file can subsequently be referred to as the value of the "command_script_file" job parameter. This is a recommended approach for achieving "separation of concern" in application architecture. 

    Code Block
    language sql
    <param name="command_script_file" value="config/live/commn/sqls/get_order.sql"/>

  • SQL code can also be specified as the value of the CDATA tag as part of the Job.XML. This approach is preferred only if the SQL  code is very small and only used by a single job.

    Code Block
    language sql
    <script language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass"> <![CDATA[ WHENEVER SQLERROR EXIT SQL.SQLCODE WHENEVER OSERROR EXIT FAILURE SELECT TO_CHAR(SYSDATE,'dd.mm.yyyy DAY') FROM DUAL; ]]> </script>

db_url

For SQL*Plus job db_url is only the Oracle DB service name or instance name

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 that should be assigned permissions to execute an SQL script.for which the SQL*Plus script is executed.

Argument: db_password

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

Argument: shell_command

The value of this variable specifies the location of the SQL*Plus binarycommand 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 By default the SOSSQLPlusJob job defines a regular expression to parse console output from the execution of SQL scripts and returns order variables 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 SQL*Plus client stdout  prompt SET period_prev IS ${PN_YEAR_PREV_PERIOD}  displays output to the console; if period_prev is 20210422, it will be parsed by the regular expression ^SETsexpression

^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 the SQL*Plus clien, e.g. -V SQL*Plus version , -S silent  no banner , -M automatic HTML output etc.

Argument: ignore_ora_messages

Its not always the case when you want to ignore error messages but if need be arise, value of the parameter ignore_ora_message will specify which ORA messages Jobscheduler can ignore.

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

If there are some expected This argument is used to ignore certain Oracle SP2 errors which you want to ignore, use and expects a comma separated list of all the SP2 errors as value for the ignore_sp2_messages parameter.SP2* error codes that should be ignored.

Argument: include_files

If you have Should some common SQL code need to be executed before the execution of the SQL*Plus code, you can define with script then the respective SQL script files can be specified by use of the include_files parameter argument. Multiple A number of files can be specified , seperated by semicolons. 

Saving Database Connection Settings in a Parameter File

It strongly recommend that a db_connection parameter file such as database_connection_sqlplus.parameter.xml is used to store all the database connection settings in a common location. This approach enables the user to manage settings at central location which can then be reused by multiple jobs.

This approach also makes it easy to maintain different settings for  development, integration and production environments.

The following shows an example database connection parameter file:

 <params >
    <param  name="db_url"      value="DORCL01"/>
    <param  name="db_user"     value="sos_scheduler"/>
    <param  name="db_password" value="sos"/>
</params>

The next example shows a JITL job where the database connection parameters are stored in an external file. In this example a "common_settings/database" directory has been created inside the JobScheduler's live folder. 

<?xml version="1.0" encoding="ISO-8859-1"?>

<job  title="Start SQL*Plus client and execute a sql*plus script" order="no">
    <settings >
        <log_level ><![CDATA[debug9]]></log_level>
    </settings>
    <description >
        <include  file="jobs/SOSSQLPlusJob.xml"/>
    </description>
    <params >
        <include  file="C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml"/>
    </params>
    <script  language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass">
        <![CDATA[
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
SELECT TO_CHAR(SYSDATE,'dd.mm.yyyy DAY') FROM DUAL;
        ]]>
    </script>
    <run_time />
</job>

Passing parameters to the SQL Script

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 scriptJobScheduler order parameters can be passed to the SQL Script. SQL Script code can be parameterized by defining variables such as ${SCHEDULER_PARAM_VARIABLE_NAME}. Variables can be set using environment variables, JobScheduler task parameters ( as described in the following example) or from JobScheduler order parameters.

<?xml version="1.0" encoding="ISO-8859-1"?>

<job  title="Start SQL*Plus client and execute a sql*plus script" order="no" name="SQLPlusExample3">
    <settings >
        <log_level ><![CDATA[debug9]]></log_level>
    </settings>
    <description >
        <include  file="jobs/SOSSQLPlusJob.xml"/>
    </description>
    <params >
        <param  name="BCY_DATE" value="28.07.2014"/>
        <param  name="PN_YEAR_PERIOD" value="2014"/>
        <param  name="PN_YEAR_PREV_PERIOD" value="2013"/>
        <include  file="C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml"/>
    </params>
    <script  language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass">
        <![CDATA[
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;
        ]]>
    </script>
    <run_time />
</job>

Parameters are not case sensitive.

SQL Script as an External File 

SQL script can be defined directly inside the Job xml as a command  parameter value but is generally better stored on the file system. JITL jobs can be configured to read PL/SQL scripts from the file system by defining the script path as a value for the command  parameter i.e. 

In the following example the PL/SQL code is saved to the filesystem in C:\app\executables\sqls\get_booking_cycle.sql and subsequently referenced using the command_script_file  parameter.

...

Passing

...

<?xml version="1.0" encoding="ISO-8859-1"?>

<job  title="Start SQL*Plus client and execute a sql*plus script" order="no">
    <settings >
        <log_level ><![CDATA[debug9]]></log_level>
    </settings>
    <description >
        <include  file="jobs/SOSSQLPlusJob.xml"/>
    </description>
    <params >
        <param  name="BCY_DATE" value="28.07.2014"/>
        <param  name="PN_YEAR_PERIOD" value="2014"/>
        <param  name="PN_YEAR_PREV_PERIOD" value="2013"/>


        <param  name="command_script_file" value="C:\app\executables\sqls\get_booking_cycle.sql"/>

        <include  file="C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml"/>
    </params>
    <script  language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass"/>
    <run_time />
</job>

...

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.

Advanced Configuration

Generic job for executing multiple SQL scripts

...

JobChain

<?xml version="1.0" encoding="ISO-8859-1"?>
<job_chain  orders_recoverable="yes" visible="yes">
    <job_chain_node  state="execute_sqlplus_script" job="JITL-SQLPlus" next_state="sucess" error_state="error"/>
    <job_chain_node  state="sucess"/>
    <job_chain_node  state="error"/>
</job_chain>

Job

...

.

...

...

<?xml version="1.0" encoding="ISO-8859-1"?>

<job  title="Start SQL*Plus client and execute a sql*plus script" order="yes">
    <settings >
        <log_level ><![CDATA[debug9]]></log_level>
    </settings>
    <description >
        <include  file="jobs/SOSSQLPlusJob.xml"/>
    </description>
    <params >
        <param  name="BCY_DATE" value="28.07.2014"/>
        <param  name="PN_YEAR_PERIOD" value="2014"/>
        <param  name="PN_YEAR_PREV_PERIOD" value="2013"/>
        <param  name="command_script_file" value="C:\app\executables\sqls\get_booking_cycle.sql"/>
        <include  file="C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml" node=""/>
    </params>
    <script  language="java" java_class="sos.scheduler.db.SOSSQLPlusJobJSAdapterClass"/>
    <run_time />
</job>

Return Variables created by the SOSSQLPLUSJob

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

...

Order : get_booking_cycle

<?xml version="1.0" encoding="ISO-8859-1"?>

<order  title="Execute get_booking_cycle.sql">
    <params >
        <param  name="command_script_file" value="C:\app\executables\sqls\get_booking_cycle.sql"/>
    </params>
     <run_time  let_run="no">
        <period  single_start="08:00"/>
    </run_time>
</order>

...

Order : get_trx_count

<?xml version="1.0" encoding="ISO-8859-1"?>

<order  title="Execute get_booking_cycle.sql">
    <params >
        <param  name="command_script_file" value="C:\app\executables\sqls\get_trx_count.sql"/>
    </params>
     <run_time  let_run="no">
        <period  single_start="08:00"/>
    </run_time>
</order>

Return parameters created by the SOSSQLPlusJob

The JobScheduler automatically creates the following order parameters, which will then be available to subsequent job steps. 

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 script ended without any error the SQL*Plus script terminates successfully.
• If std_errorerr_output is not empty then the exit code will be set to the value 99.
• If either SP2 or an ORA- error occurs * 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

...