...
Following is the most basic example of the JITL JobSchedulerPLSQLJob. Following job is executing an PL/SQL unanimous code bloc. We are selecting current system date and displaying it on stdout as order_date.
Code Block | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
<?xml version="1.0" encoding="ISO-8859-1"?> <job title="Execute PL/SQL procedure" order="yes"> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <!-- Database connection parameters i.e. db_url, db_user, db_password --> <param name="db_url" value="jdbc:oracle:thin:@:1521:DORCL01"/> <param name="db_user" value="sos_scheduler"/> <param name="db_password" value="sos"/> <!-- 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> |
...
Parameters
The JobSchedulerPLSQLJob needs following parameters.
...
- This parameter defines a reguler expression to parse dbms_output from PL/SQL execution and set the order parameters for subsequent job steps. i.e. 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".
...
Database connection settings as parameter file
...
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
...
Warning |
---|
When PL/SQL code is part of Job XML file, then parameter should be defined as \${SCHEDULER_PARAM_PARAMETER_NAME}. If PL/SQL code is read from filesystem paramter can be defined without \ |
...
PL/SQL script as external file
...
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> |
Passon 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 result of PL/SQL execution i.e. calculated date , calculated parameter from tables etc. By default the JobSchedulerPLSQL job defines a reguler expression to parse dbms_output from PL/SQL execution and set the order parameters for subsequent job steps. i.e. 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". All the dbms_output statements matching with reguler expression ^SETs+(\\s)\\s*ISs(.*)$ will be set as order_parameters.
Advance Congifuration
Generic Job to executed multiple PL/SQLs
The JobSchedulerPLSQLJob can be configured as generic node inside a JobChain and executable PL/SQL script can be defined as order parameter. Following is example of such a generic job. Following JobChain has job node execute_plsql , two orders get_order_date and get_last_booking_date, each scheduled to be excecuted on different time. Both the order are configured with different PL/SQL script file i.e. get_order_date.sql and get_last_booking_date.sql.
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>
...