Page History

...

<?xml version="1.0" encoding="ISO-8859-1"?>

<job  title="ExecuteStart PL/SQL procedureSQL*Plus client and execute a sql*plus script" order="no">
    <settings >
        <log_level ><![CDATA[debug9]]></log_level>
    </settings>
    <description >
        <include  file="jobs/JobSchedulerPLSQLJobSOSSQLPlusJob.xml"/>
    </description>
    <params >
	
	    <!-- Database connection parameters i.e. db_url, db_user, db_password --<param  name="BCY_DATE" value="28.07.2014"/>
        <include<param  live_filename="../common_settings/database/database_connection.params.xml" node="PN_YEAR_PERIOD" value="2014"/>
		
	    <!-- Parameter can be passed by task or as order param -->
		<param  name="date_maskPN_YEAR_PREV_PERIOD" value="YYYYMMDD_HH24MI2013"/>
		
        <!-- PL/SQL script from filesystem -->
	

        <param  name="command_script_file" value="C:/\app/executables/plsql/\executables\sqls\get_orderbooking_datecycle.sql"/>       	
 
        <!-- dbms_output to JobScheduler Order parameter parser regex -->
        <param  name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$ <include  file="C:\Program Files\sos-berlin.com\jobscheduler\djsmp10_1.7.4274_4274\scheduler_data\config\live\common_settings\database\database_connection_sqlplus.parameter.xml"/>
    </params>
    <script  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClassSOSSQLPlusJobJSAdapterClass"/>
    <run_time />
</job>

Passing Result of

...

SQL script execution as parameter to subsequent job steps

JobScheduler jobs can create and update JobScheduler Order parameters. The JobSchedulerPLSQLJob JobSchedulerSQLPlusJob  can also pass on the result of PL/ SQL script execution i.e. calculated dates, parameters calculated from tables, etc. By default the JobSchedulerPLSQL JobSchedulerSQLPlus job defines a regular expression to parse dbms_console output from the execution of PL/SQLs SQL Script and sets order parameters for subsequent job steps. For example, the DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date) dbms ouput SQL*Plus client stdout {code}prompt SET period_prev IS ${PN_YEAR_PREV_PERIOD};{code } displays the output on console; if  SET orderperiod_date prev is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ and return the orderperiod_dateprev="20140915" order parameter.  All dbms_output stdout statements matching the ^SETs+(\\s)\\s*ISs(.*)$ regular expression will be set as order_parameters.

Advance Configuration

Generic job for executing multiple

...

SQL scripts

The JobSchedulerPLSQLJob JobSchedulerSQLPlusJob can be configured as a generic node inside a job chain and executable PL/ SQL script can be defined as an order parameter. The following example shows such a generic job. The job chain has a job node- execute_plsql sql - two orders - get_orderbooking_date cycle and getcount_lasttrx_booking_daterecords . Each order is scheduled to be executed at a different time. Both the orders are configured to use a different PL/ SQL script file i.e. get_orderbooking_datecycle.sql and get count_lasttrx_booking_daterecords.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>

...