Job JobSchedulerPLSQLJob

DRAFT

Introduction 

The JITL JobSchedulerPLSQLJob job provide an standardized and parameterized  interface to execute Oracle PL/SQLs.  The JobScheduler offers out of the box capability to execute PL/SQLs, pass parameters to the PL/SQL or collect and passon the results of a PL/SQL execution to next job step as JobScheduler Order parameter.  The JobSchedulerPLSQLJob can be used to execute existing PL/SQL files just by referring them in the command parameter. 

Simple JITL PL/SQL Job Example

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.

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

command

• The PL/SQL code be 

  • saved to a separate file i.e. get_order.sql , and subsequently sql file can be referred as the value of the job parameter "command", this is a recommended  approach to achieve "separation of concern" in application architecture. 

     <param  name="command" value="config/live/commn/sqls/get_order.sql"/>

  • PL/SQL code can also be specified as the value of the parameter command, entire PL/SQLcan be written as part of the Job.XML, this approach is preferred if PL/SQL code is very small and used by single job.

     <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;
    	"/>

db_url

JITL will need an standard JDBC database connection string i.e. jdbc:oracle:thin:@localhost:1521:XE

db_user

DB Username which has appropriate  database right to execute PL/SQL code. 

db_password

Password for the DB user defined in the db_user parameter.

variable_parser_reg_expr

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

It strongly recommend to create a db_connection parameter file i.e. database_connection.parameter.xml and store all the Database connection settings at common location. This approach enable user to manage settings at central location and refereed by multiple jobs.

It is  also easy to maintain different settings for  development, integration and production environment.

Following is the example of a database connection parameter file

 <params >        
        <param  name="db_url"      value="jdbc:oracle:thin:@:1521:DORCL01"/>
        <param  name="db_user"     value="sos_scheduler"/>
        <param  name="db_password" value="sos"/>	
 <params >

Following is  the example of a JITL job with database connection parameter stored in the external file. In following example an directory is being created in side the live folder as "common_settings/database". 

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

Jobscheduler order parameters can be passed to the PL/SQL. PL/SQL code can be parameterized by defining variables as ${SCHEDULER_PARAM_VARIABLE_NAME}. Variables can be set from environment variables, JobScheduler task parameter ( as described in the following example) or from JobScheduler order parameter.

<?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 Code --> 
       <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;   "/>
 
        <!-- 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>

PL/SQL script as external file 

PL/SQL code can be defined directly inside the Job xml as value of command  parameter or better PL/SQL scripts are stored at filesystem. JITL job can be configured to read PL/SQL script from filesystem by defining path of the script as value of command  parameter i.e. 

In Following example the PL/SQL code is save at filesystem in C:\app\executables\plsql\get_order_date.sql and subsequently refereed by the command  parameter.

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

  JITL-PLSQL.job_chain.xml Expand source

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

  JITL-PLSQL.job.xml Expand source

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

  JITL-PLSQL,get_order_date.order.xml Expand source

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

  JITL-PLSQL,get_last_order_date.order.xml Expand source

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

The JobScheduler automatically creates  following order parameters. Below described parameters will be available to subsequent job steps as order parameter. 

sql_error

• The parameter sql_error will contain all the error messages during PL/SQL execution. If no error occurs then the parameter will be empty.

std_out_output

• The parameter std_out_output will contain all the messages spooled to stdout by PL/SQL.