Page History

...

Introduction 

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

A Simple JITL PL/SQL Job Example

Following is the most The following example shows a basic example of the JITL JobSchedulerPLSQLJob. Following job is executing an JobSchedulerPLSQLJob. It executes PL/SQL unanimous code bloc. We are selecting anonymous code blocks - selecting the current system date and displaying it on stdout as order_date.

...

Parameters

The JobSchedulerPLSQLJob needs requires the following parameters.:

command

• The PL/SQL code be can be:
  

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

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

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

    Code Block
    language sql
    <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 needs a standard JDBC database connection string i.e. jdbcsuch as jdbc:oracle:thin:@localhost:1521:XE

db_user

DB Username which has appropriate  database right to execute necessary database permission for executing the PL/SQL code. 

db_password

Password The password for the DB user defined in the db_user parameter.

variable_parser_reg_expr

This parameter defines a reguler expression to parse regular expression for parsing the dbms_output from the PL/SQL execution and set sets the order parameters for subsequent job steps.  i.e.   For example, 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".

...

Saving Database Connection Settings in a Parameter File

It strongly recommend to create that a db_connection parameter file i.e. such as database_connection.parameter.xml and is used to store all the Database database connection settings at in a common location. This approach enable enables the user to manage settings at central location and refereed can then be reused by multiple jobs.

It is  also This approach also makes it easy to maintain different settings for  development, integration and production environmentenvironments.

Following is the example of a The following shows an example 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 The next example shows a JITL job with where the database connection parameter parameters are stored in the an external file. In following example an directory is being created in side the live folder as this example a "common_settings/database" directory has been created inside the JobScheduler's live folder. 

<?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 JobScheduler order parameters can be passed to the PL/SQL. PL/SQL code can be parameterized by defining variables such as ${SCHEDULER_PARAM_VARIABLE_NAME}. Variables can be set from using environment variables, JobScheduler task parameter parameters ( as described in the following example) or from JobScheduler order parameterparameters.

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

...

an 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 but is generally better stored on the file system. JITL jobs can be configured to read PL/SQL script scripts from filesystem the file system by defining path of the script path as a value of for the 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>

...

Passing 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 pass on the result of PL/SQL execution i.e. calculated date dates, parameters calculated parameter from tables, etc. By default  the the JobSchedulerPLSQL job defines a reguler regular expression to parse dbms_output from the execution of PL/SQL execution SQLs and set the sets order parameters for subsequent job steps.  i.e. the dbms ouput For example, the DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date) dbms ouput  displays the output on console; if  SET order_date is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$  will result as order parameter order and return the order_date="20140915" order parameter.  All the dbms_output statements matching with reguler expression the ^SETs+(\\s)\\s*ISs(.*)$ $ regular expression will be  set set as order_parameters.

Advance

...

Configuration

Generic

...

job for executing multiple PL/SQLs 

The JobSchedulerPLSQLJob can be configured as a generic node inside a JobChain job chain and executable PL/SQL script can be defined as an order parameter. Following is The following example of shows such a generic job. Following JobChain The job chain has a job node node- execute_plsql , - two orders - get_order_date and get_last_booking_date,  each . Each order is scheduled to be excecuted on executed at a different time. Both the order orders are configured with to use a different PL/SQL script file i.e. get_order_date.sql and get_last_booking_date.sql. 

...

• How to work with PL/SQL and the Oracle DBMS
• How to run Oracle Stored Procedures using PL-SQL

path