Status | ||||
---|---|---|---|---|
|
Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Introduction
The JobSchedulerPLSQLJob JITL job provides a standardized and parameterized interface for executing Oracle PL/SQLs statements. The JobScheduler offers out of the box capability to execute PL/SQLs, passing parameters to the PL/SQL or collecting and 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.
...
It strongly recommend that a db_connection parameter file such as database_connection.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.
...
PL/SQL code can be defined directly inside the Job xml as value of 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 Following the following example the PL/SQL code is save at saved to the filesystem in C:\app\executables\plsql\get_order_date.sql and subsequently refereed by referenced using 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
...
PL/SQL
...
results to subsequent job steps as parameters
JobScheduler jobs can create and update JobScheduler Order parameters. The JobSchedulerPLSQLJob can also pass on the result of PL/SQL execution i.e. calculated dates, parameters calculated from tables, etc. By default the JobSchedulerPLSQL job defines a regular expression to parse dbms_output from the execution of PL/SQLs and sets order parameters for subsequent job steps. 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(.*)$ and return the order_date="20140915" order parameter. All dbms_output statements matching the ^SETs+(\\s)\\s*ISs(.*)$ regular expression will be set as order_parameters.
...
Advanced Configuration
Generic job for executing multiple PL/SQLs
...
JobChain
Code Block language xml title JITL-PLSQL.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_plsql" job="JITL-PLSQL" 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-PLSQL.job.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <job title="Execute PL/SQL procedure" order="yes"> <settings > <log_level ><![CDATA[debug9]]></log_level> </settings> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <!-- Parameter can be passed by task or as order param --> <param name="date_mask" value="YYYYMMDD_HH24MI"/> <!-- Database connection parameters i.e. db_url, db_user, db_password --> <include live_file="../common_settings/database/database_connection.params.xml" node=""/> <!-- 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>
Order : get_order_date
Code Block language xml title JITL-PLSQL,get_order_date.order.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <order title="Calculate Order Date"> <params > <!-- PL/SQL script file --> <param name="command" value="C:/app/executables/plsql/get_last_booking_date.sql"/> </params> <run_time let_run="no"> <period single_start="08:00"/> </run_time> </order>
Order : get_last_booking_date
Code Block language xml title JITL-PLSQL,get_last_order_date.order.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <order title="Calculate last booking date"> <params > <!-- PL/SQL script file --> <param name="command" value="C:/app/executables/plsql/get_last_booking_date.sql"/> </params> <run_time let_run="no"> <period single_start="11:00"/> </run_time> </order>
Return parameters created by JobSchedulerPLSQLJob
Standalone PL/SQL Jobs
If PL/SQL code needs to be parameterized by a job parameter the syntax for parameter substitute is different compare to order jobs.
Since the syntax of suffixing an order parameter for SCHEDULER_PARAM is not required the parameter name can directly be substituted in the PL/SQL code.
See the following example for two variant for standalone PL/SQL code.
PL/SQL Code as script
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job order="no" title="test">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<params >
<param name="testparam" value="test"/>
<param name="db_class" value="SOSOracleConnection"/>
<param name="db_driver" value="oracle.jdbc.driver.OracleDriver"/>
<param name="db_url" value="jdbc:oracle:thin:@8of9:1521:TEST"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
</params>
<script language="java" java_class_path="" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass">
<![CDATA[
BEGIN
INSERT INTO T_SOS_TEST VALUES ('${testparam}');
END;
]]>
</script>
<run_time />
</job>
|
PL/SQL Code as command
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job order="no" title="test">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<params >
<param name="testparam" value="test_command"/>
<param name="db_class" value="SOSOracleConnection"/>
<param name="db_driver" value="oracle.jdbc.driver.OracleDriver"/>
<param name="db_url" value="jdbc:oracle:thin:@8of9:1521:TEST"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
<param name="command" value="BEGIN INSERT INTO T_SOS_TEST VALUES ('\${testparam}'); END;"/>
</params>
<script language="java" java_class_path="" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Return parameters created by the JobSchedulerPLSQLJob
The JobScheduler automatically creates the following order parameters, which will be available to subsequent job The JobScheduler automatically creates the following order parameters, which will be available to subsequent job steps as order parameters.
...
- How to work with PL/SQL and the Oracle DBMS
- How to run Oracle Stored Procedures using PL-SQL/SQL
- The JobSchedulerPLSQLJob JITL job documentation