Introduction 

The JITL PLSQLJob provides a parameterizable interface for executing Oracle® PL/SQL statements.

JS7 offers out-of-the-box capabilities:

• for executing PL/SQL,
• for passing arguments to PL/SQL,
• for collecting and passing on the results of PL/SQL execution to subsequent jobs,
• for using a JS7 - Credential Store.

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

The job supports encryption of database credentials if used with a Hibernate configuration file, see JS7 - How to encrypt and decrypt Database Credentials.

Changes

• Starting from Release 2.0.0 the PLSQLJob
  • is implemented on top of the JDBC Interface of the Oracle® JDBC Driver, 
  • with a commit operation being performed by the Oracle® JDBC Driver when a connection is closed without an explicit commit or rollback.
• Starting from Release 2.7.1 the PLSQLJob
  • is implemented using Hibernate,
  • with no commit or rollback operation being performed when the connection is closed.
• Starting from Release 2.7.2 the PLSQLJob
  • makes use of the Hikari connection pool that ships with Hibernate,
  • performs a rollback operation when a connection is closed,
  • closes the connection in the event that a cancel/force or suspend/force operation is performed on the order.
  • JITL-743 - Getting issue details... STATUS

Usage

When defining the job either:

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

or

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

Example

Download: dbPLSQLExecution.json

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

Documentation

The Job Documentation including the full list of arguments can be found under: 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.

The following PL/SQL code creates output that can be parsed by use of the variable_parser_reg_expr  argument to create workflow variables:

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;

The following PL/SQL code creates a cursor that can be used to store the result set to a file when specifying the result_set_as_variables argument with one of the values csv, xml or json. In addition, the result_file argument is used to specify the file which selected data is written to.

DECLARE
  return_cursor SYS_REFCURSOR;
BEGIN
  OPEN return_cursor FOR 'SELECT FIRST_NAME, LAST_NAME, AGE FROM EMPLOYEES';
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 a database schema which has been 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(.*)$

and 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 stating ${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 specifying 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 the syntax: ${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 writing to the stdout channel that is checked by the PLSQLJob for patterns which indicate return values. The output is parsed by the PLSQLJob using a regular expression that is specified by the argument variable_parser_reg_expr. This 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 in output such as:

SET order_date IS 2021-05-04

The output will be parsed by the regular expression:

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

and 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

This job automatically creates the following order variables which 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. 

Cancellation

The following operations are available:

• The cancel operation is applied to the order, not to the PLSQLJob. In case of cancellation the job will continue until completion and the order will be set to the failed state.
• The cancel/force operation is applied to the order and to the job. The PLSQLJob will be forcibly terminated, transactions are rolled back, the database connection will be closed and the DBMS session will be terminated. The order will be set to the failed state.

Further Resources

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