Product Knowledge Base

Space shortcuts

Page tree

Versions Compared

Old Version 21

changes.mady.by.user Mahendra Patidar

Saved on

compared with

New Version Current

changes.mady.by.user Mahendra Patidar

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

titleWork in progress

 

Status
colourYellow
titleDraft

Table of Contents
outlinh1. true
outlinh1. true
1printablefalse
2stylh1. none
3indent20px

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.

 

Code Block
languagexml
titleSimple JobSchedulerPLSQLJob
linenumberstrue
collapsetrue
<?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;
		"/>
 
        <param  name="variable<!-- 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 requires the following parameters.:
Name
Title
Mandatory
Default
Example
command
PL/SQL statements to be executed
true
 
select sysdate from dual
db_url
JDBC connection string
true
 
jdbc:oracle:thin:@localhost:1521:XE
db_user
User name for database access
true
 
db username
db_password
Password for database access
true
 
db password
variable_parser_reg_expr
Regular expression to parse dbms_output and
set order parameters for next job steps
false^SETs+(\\s)\\s*ISs(.*)$
 
 
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
      languagesql
       <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
      languagesql
      collapsetrue
       <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 
...
needs a standard JDBC database connection string 
...
such as jdbc:oracle:thin:@localhost:1521:XE
db_user
DB Username which has 
...
necessary database permission for executing the PL/SQL code. 
db_password
...
The password for the DB user defined in the db_user parameter.
variable_parser_reg_expr
This parameter defines a 
...
regular expression for parsing the dbms_output from the PL/SQL execution and 
...
sets the order parameters for subsequent job steps.
...
  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 which 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:
 
 Code Block
languagesql
titledatabase_connection.parameter.xml
collapsetrue
 <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 "common_settings/database"this example a "common_settings/database" directory has been created inside the JobScheduler's live folder. 
 Code Block
languagexml
titleSimple JobSchedulerPLSQLJob with database_connection_settings file
collapsetrue
<?xml version="1.0" encoding="ISO-8859-1"?>
<job  title="Execute PL/SQL procedure" order="yesno">
    <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.
 
 Code Block
languagexml
titlePassing variables to the PL/SQL
collapsetrue
<?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;   "/>
 
    </params>
    <script  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
    <run_time />
</job>


 Tip
parameters can also be defined with following syntax
  • %parameter_name%
  • ${SCHEDULER_PARAM_parameter_name}
  • parameters are not case sensitive
 Warning
When PL/SQL code is part of Job XML file, then parameter should be defined as \${SCHEDULER_PARAM_PARAMETER_NAME}. If PL/SQL code is read from filesystem paramter can be defined without \ 
 
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.
 
    <!-- 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>


 Tip
Parameters can also be defined with following syntax:
  • %parameter_name%
  • ${SCHEDULER_PARAM_parameter_name}
Parameters are not case sensitive.
 Warning
When PL/SQL code is part of Job XML file, then parameters should be defined in the form \${SCHEDULER_PARAM_PARAMETER_NAME}. If PL/SQL code is read from file system, the parameter can be defined without the "\" 
PL/SQL script as an External File 
PL/SQL code can be defined directly inside the Job xml as a command  parameter value but is generally better stored on the file system. JITL jobs can be configured to read PL/SQL scripts from the file system by defining the script path as a value for the command  parameter i.e. 
In the following example the PL/SQL code is saved to the filesystem in C:\app\executables\plsql\get_order_date.sql and subsequently referenced using the command  parameter.
 
 Code Block
languagexml
titlePassing variables to the PL/SQL
collapsetrue
<?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
 Code Block
languagexml
titlePassing variables to the PL/SQL
collapsetrue
<?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  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass <!-- dbms_output to JobScheduler Order parameter parser regex -->
        <param  name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
    <run_time />
</job>

 
...
params>
    <script  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
    <run_time />
</job>

Passing PL/SQL results to subsequent job steps as parameters
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 Topics
...
Advanced 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 such a generic job
...
JobChain
 Code Block
languagexml
collapsetrue
<?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>
shows such a generic job. The job chain has a job node- execute_plsql - two orders - get_order_date and get_last_booking_date. 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_order_date.sql and get_last_booking_date.sql. 
  • JobChain
     Code Block
    languagexml
    titleJITL-PLSQL.job_chain.xml
    collapsetrue
    <?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
    languagexml
    titleJITL-PLSQL.job.xml
    collapsetrue
    <?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
    languagexml
    titleJITL-PLSQL,get_order_date.order.xml
    collapsetrue
    <?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
    languagexml
    titleJITL-PLSQL,get_last_order_date.order.xml
    collapsetrue
    <?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
languagexml
titleplsql_job_param_script.job.xml
collapsetrue
<?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;
Job
...
languagexml
collapsetrue
...

        
...
]]>
    </script>
    <run_time />
</job>

PL/SQL Code as command
 Code Block
languagexml
titleplsql_job_param_command.job.xml
collapsetrue
<?xml version="1.0" encoding="ISO-8859-1"?>

<job  order="no" title="test">
    <settings >
        
...
<log_level ><![CDATA[debug9]]></log_level>
    </
...
settings>
    
...
<params >
   
...
 
...
    
...
Job
 Code Block
languagexml
collapsetrue
 
 
  • Order : get_order_date
 Code Block
languagexml
collapsetrue
<?xml version="1.0" encoding="ISO-8859-1"?>
<order  title="Calculate last booking date">
    <params <param  name="testparam" value="test_command"/>
        <param  name="db_class" value="SOSOracleConnection"/>
        <param  name="commanddb_driver" value="C:/app/executables/plsql/get_last_booking_date.sqloracle.jdbc.driver.OracleDriver"/>
    </params>
    <run_time<param  let_run="no"name="db_url" value="jdbc:oracle:thin:@8of9:1521:TEST"/>
        <period<param  single_start="11:00"/>
    </run_time>
</order>
 
  • Order : get_last_booking_date
 Code Block
languagexml
collapsetrue
<?xml version="1.0" encoding="ISO-8859-1"?>
<order  title="Calculate Order Date">
    <params name="db_user" value="scheduler"/>
        <param  name="db_password" value="scheduler"/>
        <param  name="command" value="C:/app/executables/plsql/get_last_booking_date.sqlBEGIN             INSERT INTO T_SOS_TEST VALUES ('\${testparam}');             END;"/>
    </params>
    <run_time  let_run="no">
        <period  single_start="08:00<script  language="java" java_class_path="" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
    <run_time </run_time>>
</order>job>

Return parameters created by the JobSchedulerPLSQLJob
The JobScheduler automatically creates  following the following order parameters. Below described parameters , which will be available to subsequent job steps as order parameterparameters. 
sql_error
  • The parameter sql_error will contain parameter contains all the error messages generated during the PL/SQL execution. If no error occurs then the This parameter will be empty if no errors occur.
std_out_output
  • The parameter std_out_output will contain parameter contains all the messages spooled to stdout by PL/SQL. 
See also: