...
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>
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
...