Introduction 

The JobSchedulerPLSQLJob JITL job provides The JITL SOSPLSQLJob provides a standardized and parameterized parameterizable interface for executing Oracle PL/SQLs SQL statements.  The JobScheduler The JS7 offers out of the box capability 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 step as a JobScheduler Order parameter.  The JobSchedulerPLSQLJob can be used to execute existing files that include PL/SQL files just by referring them in the command parameter. 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.

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

...

Parameterization

The JobSchedulerPLSQLJob requires SOSPLSQLJob supports the following parametersarguments:

Argument: command

• The PL/SQL code can be:
  

  • saved to a separate file such as get_order.sql . This file can subsequently be referred to as the value of the "command" job parameter. This is a recommended 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 parameter, with the entire PL/SQL being written as part of the Job.XML. 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; "/>

Argument: db_url

JITL needs a standard JDBC database connection string such as jdbc:oracle:thin:@localhost:1521:XE

Argument: db_user

DB Username which has necessary database permission for executing the PL/SQL code. 

Argument: db_password

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

Argument: variable_parser_reg_expr

This parameter defines This argument specifies a regular expression for parsing the dbms_output from the PL/SQL execution and sets the order parameters for subsequent job steps.  of 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 dbms ouput statement

EXEC DBMS_OUTPUT.PUT_LINE( 'SET order_date IS ' ||

...

TO_

...

CHAR(CURRENT_DATE, 'YYYY-MM-DD') );

displays the following output to the console:

SET order_date is 2021-05-04

The output 20140915, it will be parsed by the regular expression

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

...

that will result in an order variable

order_date = "

...

2021-05-04"

...

Saving Database Connection Settings in a Parameter File

...