Introduction 

The JITL PLSQLJob 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 PL/SQL execution to next jobs,
• to use a JS7 - Credential Store.

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

Usage

When defining the job consider

• to invoke the Wizard that is available from the job properties tab in the Configuration view and to select the JITL PLSQLJob and respective arguments from the Wizard

or

• to specify the JITL job class and com.sos.jitl.jobs.db.oracle.PLSQLJob Java class name, then add arguments as explained from the below documentation.

Example

Download: dbPLSQLExecution.json

The following example explains basic use of the PLSQLJob. It executes PL/SQL anonymous code blocks - selecting the current system date and writing it to the stdout channel.

Documentation

Job Documentation including the full list of arguments: https://www.sos-berlin.com/doc/JS7-JITL/PLSQLJob.xml

The PLSQLJob supports the following arguments:

The PLSQLJob can be used with a credential store to hold sensitive arguments. For use of the credential_store_* arguments see JS7 - Use of Credential Store with JITL Jobs.

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

PL/SQL code can be used from from a file that is assigned this argument, for example:

./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.

Manage Arguments

Arguments from Job Resources

For example, database connection settings can be added to JS7 - Job Resources in order to be shared by a number of jobs. This allows central management of connections settings and limits visibility of passwords with individual database jobs.

The Job Resource is then assigned the job or workflow. As the same argument names have been used as for the Pl/SQL job they are automatically mapped to the job.

Passing Arguments to PL/SQL Code

Arguments can be passed to PL/SQL statements. Such arguments can be added to the PL/SQL code and will be replaced at run-time. The syntax for arguments includes to state ${VARIABLE}, such variables are replaced from any location in the PL/SQL script code. For example, a job argument or order variable with the name $dateFormat to specify the output format of a date can be added to PL/SQL script code like this:

DECLARE 
    v_order_date VARCHAR2(16) := SYSDATE;       
BEGIN 
    /* pass variables to PL/SQL by using: ${VARIABLE} */
    SELECT to_char(SYSDATE, '${dateFormat}' )    
      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;

The above example can be used

• for PL/SQL code included as the value of the command argument.
• for PL/SQL code included with a script file that is referenced by the command_script_file argument.

Manage Return Values

Passing PL/SQL results to subsequent instructions and jobs

JS7 jobs can create return values that are available for later instructions and jobs in a workflow. The PLSQLJob can pass on the result of PL/SQL execution, e.g. calculated dates, by use of the DBMS_OUTPUT package.

This package allows to write to the stdout channel that is checked by the PLSQLJob for patterns indicating return values. The output is parsed by the PLSQLJob using a regular expression that is specified by the argument variable_parser_reg_expr. that defaults to ^SETs+(\\s)\\s*ISs(.*)$.

The following line of Pl/SQL code

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

results e.g. in the output

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 instructions and jobs can make use of the $order_date variable.

Return Variables automatically available from the PLSQLJob

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

Return Variable: sql_error

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

Return Variable: std_out_output

• The std_out_output order variable contains the messages spooled to the stdout channel by PL/SQL. 

Further Resources

• JS7 - JITL SQLPLUSJob
• JS7 - JITL SQLExecutorJob
• JS7 - JITL Common Parameters
• JS7 - Job Resources
• JS7 - Use of Credential Store with JITL Jobs