JS7 JobScheduler

Space shortcuts

Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 8 Next »

Introduction 

The JITL SOSPLSQLJob provides a parameterizable interface for executing Oracle PL/SQL statements. The JS7 offers out of the box capabilities

  • to execute PL/SQL,
  • to pass arguments to PL/SQL
  • to collect and to pass on the results of a PL/SQL execution to a next job.  

The SOSPLSQLJob can be used to execute files that include PL/SQL statements.

Usage

The following example shows a basic example of the SOSPLSQLJob. It executes PL/SQL anonymous code blocks - selecting the current system date and writing it to stdout.

Arguments

The SOSPLSQLJob supports the following arguments:

Name

Purpose

Required

Default Value

Example

command

PL/SQL statements to be executed

One of the arguments has to be specified


SELECT SYSDATE FROM DUAL

command_script_filePL/SQL statements from a file
/home/sos/some_script.sql

db_url

JDBC connection string

Either a DB URL or a Hibernate configuration file is used.

User and password for database access can be specified by arguments with the same name, can be omitted if Oracle Wallet is used or can be specified with the Hibernate configuration file


jdbc:oracle:thin:@localhost:1521:XE

db_user

User name for database access


scott

db_password

Password for database access


tiger
hibernate_configuration_fileReplaces db_url, db_user, db_password from a hibernate configuration file.
./config/private/hibernate.cfg.xml

variable_parser_reg_expr

Regular expression to parse output from the DBMS_OUTPUT package and to set order variables for next jobs

false^SETs+(\\s)\\s*ISs(.*)$



credential_store_fileLocation of a credential store file (*.kdbx)false
./config/private/jobs.kdbx
credential_store_keyLocation of a credential store file (*.key)false
./config/private/jobs.key

Argument: command

PL/SQL code can be specified as the value of the command argument:

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;

Argument: command_script_file

The PL/SQL code could be used from from a file that is assigned this argument:

./config/get_order.sql

Argument: db_url

The job requires a standard JDBC database connection string such as jdbc:oracle:thin:@localhost:1521:XE

Argument: db_user

This argument specifies the user account for the database schema that is assigned the required privileges to execute the PL/SQL code.

Argument: db_password

This argument specifies the password for the user account given with the db_user argument.

Argument: variable_parser_reg_expr

This argument specifies a regular expression for parsing the output created by the the DBMS_OUTPUT package from the PL/SQL statements. For each match an order variable is created that is available for subsequent jobs. For example, the statement

EXEC DBMS_OUTPUT.PUT_LINE( 'SET order_date IS ' || TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') );

writes the following output to the console:

SET order_date is 2021-05-04

The output will be parsed by the regular expression

^SETs+(\\s)\\s*ISs(.*)$

that will result in an order variable

order_date = "2021-05-04"

Subsequent jobs can make use of the $order_date variable.

Saving Database Connection Settings in a Parameter File

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

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

The following shows an example database connection parameter file:

database_connection.parameter.xml 
 <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 >


The next example shows a JITL job where the database connection parameters are stored in an external file. In this example a "common_settings/database" directory has been created inside the JobScheduler's live folder. 

JobSchedulerPLSQLJob with database_connection_settings file 
<?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 such as ${SCHEDULER_PARAM_VARIABLE_NAME}. Variables can be set using environment variables, JobScheduler task parameters ( as described in the following example) or from JobScheduler order parameters.


Passing variables to the PL/SQL 
<?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 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.


Passing variables to the PL/SQL 
<?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 PL/SQL results to subsequent job steps as parameters

JobScheduler jobs can create and update JobScheduler Order parameters. The JobSchedulerPLSQLJob  can also pass on the result of PL/SQL execution i.e. calculated dates, parameters calculated from tables, etc. By default the JobSchedulerPLSQL job defines a regular expression to parse dbms_output from the execution of PL/SQLs and sets order parameters for subsequent job steps. 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(.*)$ and return the order_date="20140915" order parameter.  All dbms_output statements matching the ^SETs+(\\s)\\s*ISs(.*)$ regular expression will be set as order_parameters.

Advanced Configuration

Generic job for executing multiple PL/SQLs 

The JobSchedulerPLSQLJob 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 - 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

    JITL-PLSQL.job_chain.xml 
    <?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 
    <?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 
    <?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 
    <?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


plsql_job_param_script.job.xml 
<?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;
        ]]>
    </script>
    <run_time />
</job>

PL/SQL Code as command

plsql_job_param_command.job.xml 
<?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_command"/>
        <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"/>
        <param  name="command" value="BEGIN             INSERT INTO T_SOS_TEST VALUES ('\${testparam}');             END;"/>
    </params>
    <script  language="java" java_class_path="" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
    <run_time />
</job>

Return Variables created by the SOSPLSQLJob

The job automatically creates the following order variables that are available to subsequent instructions and jobs. 

Return Variable: sql_error

  • The sql_error parameter contains all the error messages generated during the PL/SQL execution. This parameter will be empty if no errors occur.

Return Variable: std_out_output

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

Further References




  • No labels