...
| Table of Contents | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
Introduction
The JobSchedulerSQLPlusJob SOSSQLPlusJob JITL job provides a standardized and parameterized interface for executing Oracle SQL*Plus scripts. The JobScheduler offers out of the box capability to execute SQL scripts, passing parameters to the SQL*Plus script or collecting and passing on the results of a script execution to next job step as a JobScheduler Order parameter. The JobSchedulerSQLPlusJob The SOSSQLPlusJob can be used to execute existing SQL*Plus scripts by referring them in the command parameter.
A Simple JITL SQL*Plus Job Example
The following example shows a basic example of the JobSchedulerSQLPlusJob SOSSQLPlusJob . It executes a simple SQL Statment - selecting the current system date and displaying it on stdout as order_date.
| 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="SQLPlusExampleSimple">
<settings >
<log_level ><![CDATA[debug1]]></log_level>
</settings>
<description >
<include file="jobs/SOSSQLPlusJob.xml"/>
</description>
<params >
<param name="db_url" value="DORCL01"/>
<param name="db_user" value="sos_scheduler"/>
<param name="db_password" value="sos"/>
</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>
|
Parameters
The JobSchedulerSQLPlusJob SOSSQLPlusJob requires the following parameters:
| # | Name | Title | Mandatory | Default | Example |
|---|---|---|---|---|---|
| 1 | command_script_file | SQL script to be executed | true |
|
|
| 2 | db_url | Database service or Instance name | true |
| DORCL01 |
| 3 | db_user | User name for database access | true |
| db username |
| 4 | db_password | Password for database access | true |
| db password |
| 5 | shell_command | SQL plus client name | true | sqlplus | |
| 6 | variable_parser_reg_expr | Regular expression to parse output and set order parameters for next job steps | false | ^SETs+(\\s)\\s*ISs(.*)$ |
|
| 7 | command_Line_options | SQL*Plus command line options | false | -S -L | |
| 8 | ignore_ora_messages | Ignore ora error messages | false | ||
| 9 | ignore_sp2_messages | ignore sp2 error messages | false | ||
| 10 | include_files | Code blocks should be executed before SQL | false |
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 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.
db_password
The password for the DB user defined in the db_user parameter.
shell_command
The value of this parameter specifies the name of SQL*Plus client. The SQL sctipt will be processed by the client. This parameter is useful when executing legacy SQL code i.e. Oracle 9i, 10g
variable_parser_reg_expr
By default the JobSchedulerSQLPlus SOSSQLPlusJob 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.
command_Line_options
The value of this parameter specifies all the command line parameters required by the SQL*Plus client i.e. -V SQL*Plus version , -S silent no banner , -M automatic HTML output etc.
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.
ignore_sp2_messages
If there are some expected Oracle SP2 errors which you want to ignore, use a comma separated list of all the SP2 errors as value for the ignore_sp2_messages parameter.
include_files
If you have some common code need to be executed before SQL*Plus code, you can define with include_files parameter, . Multiple files can be specify specified, seperated by semicolon separatedsemicolons.
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 and which can then be reused by multiple jobs.
...
| 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
JobScheduler 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
PL/SQL code script can be defined directly inside the Job xml as value ofa 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 Following the following example the PL/SQL code is save at saved to the filesystem in C:\app\executables\sqls\get_booking_cycle.sql and subsequently refereed by referenced using the command_script_file parameter.
...
| 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 >
<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
JobScheduler jobs can create and update JobScheduler Order parameters. The JobSchedulerSQLPlusJob can 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
The JobSchedulerSQLPlusJob SOSSQLPlusJob can be configured as a generic node inside a job chain and executable SQL script can be defined as an order parameter. The following example shows such a generic job. The job chain has a job node- execute_sql - two orders - get_booking_cycle and count_trx_records . Each order is scheduled to be executed at a different time. Both the orders are configured to use a different SQL script file i.e. get_booking_cycle.sql and count_trx_records.sql.
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 as order parameters.
sql_error
- The sql_error parameter contains all the error messages generated during the SQL script execution. This parameter will be empty if no errors do not occur.
std_out_output
- The std_out_output parameter contains all the messages spooled to stdout by SQL Script.
std_err_output
- The std_out_output parameter contains all the messages spooled to stdout by SQL Script.
exit_code
- The exit_code will be 0 If script ended without any error
- If std_error_output is not empyt empty the exit code will be set to 99
- If either SP2 or an ORA- error occurs the exit code will be set to 98
See also:
- How to work with PL/SQL and the Oracle DBMS
- How to run Oracle Stored Procedures using PL/SQL
- The JobSchedulerSQLPlusJob JITL job documentation
...