Page History
Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
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.
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:
Name | Purpose | Required | Default Value | Example |
---|
Parameters
The SOSSQLPlusJob requires the following parameters:
# | Name | Title | Mandatory | Default | Example |
---|---|---|---|---|---|
| Path to the SQL script to that should be executed | true |
| ||
| Database service name or Instance instance name | true |
| ||
| User name for database access | true | db username | 4scott | |
| Password for database access | true | db password | 5tiger | |
shell_command | Path to the SQL*Plus command line invocationutility | true | sqlplus | ||
6 |
| Regular expression to parse output and to set order variables for subsequent jobs | false | ^SETs+(\\s)\\s*ISs(.*)$ | |
7 | command_ Lineline_options | SQL*Plus command line options | false | -S -L | |
8 | ignore_ora_messages | Ignore Oracle error messages | false9 | ||
ignore_sp2_messages | ignore Ignore sp2 error messages | false10 | |||
include_files | Code blocks that should be executed included before executing the SQL*Plus script | false |
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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<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 | ||||||
---|---|---|---|---|---|---|
| ||||||
<?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 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.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?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: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
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?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
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?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 | ||||||
---|---|---|---|---|---|---|
| ||||||
<?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 value99
. - 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
...