...
| 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"/>
<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; "/>
</params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
| Tip |
|---|
parameters can also be defined with following syntax
|
...
| 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"/>
</params>
<script </params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"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 Topics
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
JobChain
Code Block language 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 | ||||
|---|---|---|---|---|
| ||||
<?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> |
- Order : get_order_date
| Code Block | ||||
|---|---|---|---|---|
| ||||
<?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> |
- Order : get_last_booking_date
| Code Block | ||||
|---|---|---|---|---|
| ||||
<?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"/> <run_time <job_chain_node state="error"/> </job> job_chain> |
...
How can results be used in subsequent jobs?
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
declare
howmany NUMBER;
p_id varchar2(20) := null;
begin
dbms_output.put_line('set variable1=value1');
p_id := '12345';
--
-- If the job or order contains the parameter
--
-- table_name=scheduler_variables
--
-- then all following selects are substituted to 'select count(*) into howmany from scheduler_variables;'
--
select count(*) into howmany from $\{SCHEDULER_PARAM_table_name\};
select count(*) into howmany from %table_name%;
select count(*) into howmany from %TABLE_NAME%;
select count(*) into howmany from $\{SCHEDULER_PARAM_TABLE_NAME\};
select count(*) into howmany from $\{scheduler_param_table_name\};
select count(*) into howmany from $\{sChEdUlEr_pArAm_tAbLe_nAmE\};
--
-- now put the results to the buffer
-- JS will get the results from the buffer
--
dbms_output.put_line('The table %table_name% has ' || howmany || ' rows.');
dbms_output.put_line('set howmany is ' || howmany);
dbms_output.put_line('set variable1 is ' || p_id);
dbms_output.put_line('set variable2 is value2');
end;
|
...
Example: PL/SQL code
- If the job or order contains the parameter table_name with the value
scheduler_variablesthen all following SQL "select" statements are substituted to:select count(*) into howmany from scheduler_variables; - If the job is started in a job chain by an order then the following parameters are added to the order
- see the parameter variable_parser_reg_expr
- For this purpose, the output of the PL/SQL statement is parsed with the regular expression:
^SET\\s+([^\\s]+)\\s*IS\\s+(.*)$
...