...
Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Introduction
The JITL JobSchedulerPLSQLJob job provide an JobSchedulerPLSQLJob JITL job provides a standardized and parameterized interface to execute interface for executing Oracle PL/SQLs statements. The JobScheduler offers out of the box capability to execute PL/SQLs, pass passing parameters to the PL/SQL or collect collecting and passon passing on the results of a PL/SQL execution to next job step as a JobScheduler Order parameter. The JobSchedulerPLSQLJob can be used to execute existing PL/SQL files just by referring them in the command parameter.
A Simple JITL PL/SQL Job Example
Following is the most The following example shows a basic example of the JITL JobSchedulerPLSQLJob. Following job is executing an JobSchedulerPLSQLJob. It executes PL/SQL unanimous code bloc. We are selecting anonymous code blocks - selecting the current system date and displaying it on stdout as order_date.
...
Parameters
The JobSchedulerPLSQLJob needs requires the following parameters.:
Name | Title | Mandatory | Default | Example |
---|---|---|---|---|
command | PL/SQL statements to be executed | true |
|
|
db_url | JDBC connection string | true |
|
|
db_user | User name for database access | true |
| db username |
db_password | Password for database access | true |
| db password |
variable_parser_reg_expr | Regular expression to parse dbms_output and set order parameters for next job steps | false | ^SETs+(\\s)\\s*ISs(.*)$ |
|
command
- The PL/SQL code be can be:
saved to a separate file i.e. such as get_order.sql , and subsequently sql . This file can subsequently be referred to as the value of the job parameter "command" , this job parameter. This is a recommended approach to achieve approach for achieving "separation of concern" in application architecture.
Code Block language sql <param name="command" value="config/live/commn/sqls/get_order.sql"/>
PL/SQL code can also be specified as the value of the command parametercommand, with the entire PL/SQLcan be SQL being written as part of the Job.XML, this . This approach is preferred if the PL/SQL code is very small and only used by a single job.
Code Block language sql <param name="command" value=" DECLARE v_order_date DATE := SYSDATE; BEGIN SELECT SYSDATE INTO v_order_date FROM DUAL; DBMS_OUTPUT.PUT_LINE(' +++ +++'); DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date); DBMS_OUTPUT.PUT_LINE(' +++ +++'); END; "/>
db_url
JITL will need an needs a standard JDBC database connection string i.e. jdbcsuch as jdbc:oracle:thin:@localhost:1521:XE
db_user
DB Username which has appropriate database right to execute necessary database permission for executing the PL/SQL code.
db_password
Password The password for the DB user defined in the db_user parameter.
variable_parser_reg_expr
This parameter defines a reguler expression to parse regular expression for parsing the dbms_output from the PL/SQL execution and set sets the order parameters for subsequent job steps. i.e. For example, the dbms ouput DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date) displays the output on console SET order_date is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ will result as order parameter order_date="20140915".
...
Saving Database Connection Settings in a Parameter File
It strongly recommend to create that a db_connection parameter file i.e. such as database_connection.parameter.xml and is used to store all the Database database connection settings at in a common location. This approach enable enables the user to manage settings at central location and refereed can then be reused by multiple jobs.
It is also This approach also makes it easy to maintain different settings for development, integration and production environmentenvironments.
Following is the example of a The following shows an example database connection parameter file:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<params > <param name="db_url" value="jdbc:oracle:thin:@:1521:DORCL01"/> <param name="db_user" value="sos_scheduler"/> <param name="db_password" value="sos"/> <params > |
Following is the example of The next example shows a JITL job with where the database connection parameter parameters are stored in the an external file. In following example an directory is being created in side the live folder as 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="Execute PL/SQL procedure" order="no"> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <!-- Database connection parameters i.e. db_url, db_user, db_password --> <include live_file="../common_settings/database/database_connection.params.xml" node=""/> <!-- PL/SQL Code --> <param name="command" value=" DECLARE v_order_date DATE := SYSDATE; BEGIN SELECT SYSDATE INTO v_order_date FROM DUAL; DBMS_OUTPUT.PUT_LINE(' +++ +++'); DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date); DBMS_OUTPUT.PUT_LINE(' +++ +++'); END; "/> <!-- dbms_output to JobScheduler Order parameter parser regex --> <param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/> </params> <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/> <run_time /> </job> |
Passing parameters to the PL/SQL
Jobscheduler JobScheduler order parameters can be passed to the PL/SQL. PL/SQL code can be parameterized by defining variables such as ${SCHEDULER_PARAM_VARIABLE_NAME}. Variables can be set from using environment variables, JobScheduler task parameter parameters ( as described in the following example) or from JobScheduler order parameterparameters.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?> <job title="Execute PL/SQL procedure" order="no"> <settings > <log_level ><![CDATA[debug9]]></log_level> </settings> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <!-- Database connection parameters i.e. db_url, db_user, db_password --> <include live_file="../common_settings/database/database_connection.params.xml" node=""/> <!-- Parameter can be passed by task or as order param --> <param name="date_mask" value="YYYYMMDD_HH24MI"/> <!-- PL/SQL Code --> <param name="command" value=" DECLARE v_order_date VARCHAR2(16) := SYSDATE; BEGIN /* recommended to set variables in the PL/SQL is with ${SCHEDULER_PARAM_VARIABLE_NAME} */ SELECT to_char(SYSDATE, '\${SCHEDULER_PARAM_DATE_MASK}' ) INTO v_order_date FROM DUAL; DBMS_OUTPUT.PUT_LINE(' +++ +++'); DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date); DBMS_OUTPUT.PUT_LINE(' +++ +++'); END; "/> <!-- dbms_output to JobScheduler Order parameter parser regex --> <param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/> </params> <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/> <run_time /> </job> |
Tip |
---|
parameters Parameters can also be defined with following syntax:
Parameters are not case sensitive. |
Warning |
---|
When PL/SQL code is part of Job XML file, then parameter parameters should be defined as in the form \${SCHEDULER_PARAM_PARAMETER_NAME}. If PL/SQL code is read from filesystem paramter file system, the parameter can be defined without the "\" |
PL/SQL script as
...
an External File
PL/SQL code can be defined directly inside the Job xml as value of command parameter or better PL/SQL scripts are stored at filesystem. JITL job but is generally better stored on the file system. JITL jobs can be configured to read PL/SQL script scripts from filesystem the file system by defining path of the script path as a value of for the command parameter i.e.
In Following example the PL/SQL code is save at filesystem in C:\app\executables\plsql\get_order_date.sql and subsequently refereed by the command parameter.
...
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?> <job title="Execute PL/SQL procedure" order="no"> <settings > <log_level ><![CDATA[debug9]]></log_level> </settings> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <!-- Database connection parameters i.e. db_url, db_user, db_password --> <include live_file="../common_settings/database/database_connection.params.xml" node=""/> <!-- Parameter can be passed by task or as order param --> <param name="date_mask" value="YYYYMMDD_HH24MI"/> <!-- PL/SQL script from filesystem --> <param name="command" value="C:/app/executables/plsql/get_order_date.sql"/> <!-- dbms_output to JobScheduler Order parameter parser regex --> <param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/> </params> <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/> <run_time /> </job> |
...
Passing Result of PL/SQL as parameter to subsequent job steps
JobScheduler jobs can create and update environment variable, JobScheduler Order parameters. The JobSchedulerPLSQLJob can also passon pass on the result of PL/SQL execution i.e. calculated date dates, parameters calculated parameter from tables, etc. By default the the JobSchedulerPLSQL job defines a reguler regular expression to parse dbms_output from the execution of PL/SQL execution SQLs and set the sets order parameters for subsequent job steps. i.e. the dbms ouput For example, the DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date) dbms ouput displays the output on console; if SET order_date is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ will result as order parameter order and return the order_date="20140915" order parameter. All the dbms_output statements matching with reguler expression the ^SETs+(\\s)\\s*ISs(.*)$ $ regular expression will be set set as order_parameters.
Advance
...
Configuration
Generic
...
job for executing multiple PL/SQLs
The JobSchedulerPLSQLJob can be configured as a generic node inside a JobChain job chain and executable PL/SQL script can be defined as an order parameter. Following is The following example of shows such a generic job. Following JobChain The job chain has a job node node- execute_plsql , - two orders - get_order_date and get_last_booking_date, each . Each order is scheduled to be excecuted on executed at a different time. Both the order orders are configured with to use a different PL/SQL script file i.e. get_order_date.sql and get_last_booking_date.sql.
...
path