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.

A Simple JITL PL/SQL Job Example

The following example shows a basic example of the JobSchedulerPLSQLJob. It executes PL/SQL anonymous code blocks - selecting the current system date and displaying it on stdout as order_date.

Parameterization

The SOSPLSQLJob supports the following arguments:

Name	Description	Mandatory	Default Value	Example
`command`	PL/SQL statements to be executed	One of the arguments has to be specified		`SELECT SYSDATE FROM DUAL`
`command_script_file`	PL/SQL statements from a file	One of the arguments has to be specified		`/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_file`	Replaces 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)\\sISs(.)$
`credential_store_file`	Location of a credential store file (*.kdbx)	false		`./config/private/jobs.kdbx`
`credential_store_key`	Location 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>

Parameters can also be defined with following syntax:

%parameter_name%
${SCHEDULER_PARAM_parameter_name}

Parameters are not case sensitive.

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.

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 parameters created by the JobSchedulerPLSQLJob

The JobScheduler automatically creates the following order parameters, which will be available to subsequent job steps as order parameters.

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.

std_out_output

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

Space shortcuts

Page tree

Introduction

A Simple JITL PL/SQL Job Example

Parameterization

Argument: `command`

Argument: `command_script_file`

Argument: `db_url`

Argument: `db_user`

Argument: `db_password`

Argument: `variable_parser_reg_expr`

Saving Database Connection Settings in a Parameter File

Passing parameters to the PL/SQL

Parameters can also be defined with following syntax:

PL/SQL script as an External File

Passing PL/SQL results to subsequent job steps as parameters

Advanced Configuration

Generic job for executing multiple PL/SQLs

Standalone PL/SQL Jobs

PL/SQL Code as script

PL/SQL Code as command

Return parameters created by the JobSchedulerPLSQLJob

sql_error

std_out_output

See also:

Space shortcuts

Page tree

JS7 - JITL SOSPLSQLJob

Introduction

A Simple JITL PL/SQL Job Example

Parameterization

Argument: command

Argument: command_script_file

Argument: db_url

Argument: db_user

Argument: db_password

Argument: variable_parser_reg_expr

Saving Database Connection Settings in a Parameter File

Passing parameters to the PL/SQL

Parameters can also be defined with following syntax:

PL/SQL script as an External File

Passing PL/SQL results to subsequent job steps as parameters

Advanced Configuration

Generic job for executing multiple PL/SQLs

Standalone PL/SQL Jobs

PL/SQL Code as script

PL/SQL Code as command

Return parameters created by the JobSchedulerPLSQLJob

sql_error

std_out_output

See also:

Argument: `command`

Argument: `command_script_file`

Argument: `db_url`

Argument: `db_user`

Argument: `db_password`

Argument: `variable_parser_reg_expr`