Table of Contents

	outlinh1. true
	outlinh1. true
1	printablefalse
2	stylh1. none
3	indent20px

Introduction

The SOSSQLPlusJob JITL job The SQLPLUSJob JITL Job Template provides a standardized and parameterized interface for executing Oracle® SQL*Plus scripts. The JobScheduler The job template offers out-of-the box capability to execute SQL scripts, passing parameters to the -box capabilities for:

executing an SQL*Plus script,
passing arguments to an SQL*Plus script

...

,
collecting results,
passing results as Order Variables to next jobs,
using a JS7 - Credential Store.

Usage

When defining the job either:

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

or

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

Example

Download: dbSQLPlusExecution.json

...

The following example shows a the basic example use of the SOSSQLPlusJob SQLPLUSJob. It executes a simple SQL Statment - selecting the current system date and displaying it on stdout as order_date.

...

language	xml
title	Simple JobSchedulerSQLPlusJob
linenumbers	true
collapse	true

...

SQL*Plus code from an .sql file and writes output to the stdout channel.

Image Added

The job arguments can be specified as follows:

Image Added

Explanation:

The command_script_file argument points to a file with, for example, the following SQL code:
- WHENEVER SQLERROR EXIT SQL.SQLCODE
  WHENEVER OSERROR EXIT FAILURE
  SELECT 'SET business_date IS ' || TO_CHAR(SYSDATE,'dd.mm.yyyy

...

- DAY')

...

- FROM

...

- DUAL;

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 class accepts the following arguments:

Parameters

The SOSSQLPlusJob requires the following parameters:

#

Name

Title

Mandatory

Default

Example1

Name	Purpose	Required	Default Value	Example

command_script_file

Path to the SQL script

to

that should be executed

true

c:\app\bin\sqls\get_upd_count.sql

2


`db_url`	Database service name or

Instance

instance name

true

DORCL01

3

db_user

User name for database access

true

db username

scott

4

db_password

Password for database access

true

db password

tiger

5

shell_command

SQL plus client name

Path to the SQL*Plus command line utility true sqlplus

6

variable_parser_reg_expr

Regular expression to parse output and

to set order

parameters for next job steps

variables for subsequent jobs

false

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

7

command_

Line

line_options SQL*Plus command line options false -S -L

8


`ignore_ora_messages`	Ignore

ora

Oracle error messages

false

9


`ignore_sp2_messages`

ignore

Ignore sp2 error messages

false

10


`include_files`	Code blocks that should be

executed

included before executing the SQL*Plus script

false

...


`credential_`

...

store_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 SQLPlus job db_url is only the Oracle DB service name or instance name

db_user

DB Username which has necessary database permission to execute SQL script.

Location of a credential store database (*.kdbx)	false		`./config/private/jobs.kdbx`
`credential_store_key`	Location of a credential store key file (*.key)	false		`./config/private/jobs.key`

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`

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 DB user defined in database user account specified with the db_user parameter argument.

Argument: `shell_command`

The value of this parameter variable specifies the name location of the SQL*Plus clientcommand line utility. The SQL sctipt *Plus script will be processed by the client. This parameter is useful when executing legacy SQL code i.e. Oracle 9i, 10gindicated 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

This 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 instructions and jobs can use of the $order_date order variable.

Argument: `command_line`

...

`_options`

The value of this parameter variable specifies all the command line parameters required by the SQL*Plus client i, e.eg. -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:

Code Block

language	sql
title	database_connection_sqlplus.parameter.xml
collapse	true

 <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.

Code Block

language	xml
title	SOSSQLPlusJob with database_connection_settings file
collapse	true

<?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:

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 ${variable} syntax. 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.

Code Block

language	xml
title	Passing variables to the SQL script
collapse	true

<?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>

Tip

Parameters can also be defined with following syntax:

%parameter_name%
${SCHEDULER_PARAM_parameter_name}

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.

Code Block

language	xml
title	Passing variables to the SQL Script
collapse	true

<?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>

Passing SQL script results to subsequent job steps as parameters

...

Passing SQL execution results to subsequent Jobs

The job allows 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.

Return Variables created by the SQLPLUSJob

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

Advanced Configuration

Generic job for executing multiple SQL scripts

...

JobChain

Code Block

language	xml
title	JITL-SQLPlus.job_chain.xml
collapse	true

<?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

Code Block

language	xml
title	JITL-SQLPlus.job.xml
collapse	true

<?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>

Order : get_booking_cycle

Code Block

language	xml
title	JITL-SQLPlus,get_booking_cycle.order
collapse	true

<?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

Code Block

language	xml
title	JITL-SQLPlus,get_trx_count.order.xml
collapse	true

<?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 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 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

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 set to the failed state.
The cancel/force operation is applied to the order and to the job. The SQLPlusJob will be forcibly terminated 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 set to the failed state. The DBMS decides about rolling back transactions when closing the client session.

Space shortcuts

Page tree

Page History

Versions Compared

Old Version 1

New Version Current

Key

Introduction

Usage

Example

Documentation

Parameters

db_url

db_user

Argument: command_script_file

Argument: db_url

Argument: db_user

Argument: db_password

Argument: shell_command

Argument: variable_parser_reg_

expr

Argument: command_line

_options

Argument: ignore_ora_messages

Argument: ignore_sp2_messages

Argument: include_files

Saving Database Connection Settings in a Parameter File

Passing parameters to the SQL Script

Passing Arguments to SQL*Plus Scripts

Parameters can also be defined with following syntax:

SQL Script as an External File

Passing SQL script results to subsequent job steps as parameters

Passing SQL execution results to subsequent Jobs

Return Variables created by the SQLPLUSJob

Advanced Configuration

Generic job for executing multiple SQL scripts

Return parameters created by the SOSSQLPlusJob

Return Variable:

sql_error

Return Variable: std_out_output

Return Variable: std_err_output

Return Variable: exit_code

See also:

Cancellation

Further Resources

Argument: `command_script_file`

Argument: `db_url`

Argument: `db_user`

Argument: `db_password`

Argument: `shell_command`

Argument: `variable_parser_reg_`

`expr`

Argument: `command_line`

`_options`

Argument: `ignore_ora_messages`

Argument: `ignore_sp2_messages`

Argument: `include_files`

`sql_error`

Return Variable: `std_out_output`

Return Variable: `std_err_output`

Return Variable: `exit_code`