...

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

JS7 offers out-of-the-box capabilities:

• to execute for executing PL/SQL,
• to pass for passing arguments to PL/SQL,
• to collect and to pass for collecting and passing on the results of PL/SQL execution to next subsequent jobs,
• to use 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.

  • Jira
    server SOS JIRA columnIds issuekey,summary,issuetype,created,updated,duedate,assignee,reporter,priority,status,resolution columns key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution serverId 6dc67751-9d67-34cd-985b-194a8cdc9602 key JITL-743

Usage

When defining the job considereither:

• 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 and add arguments as explained from in the documentation below documentation.

Example

Download: dbPLSQLExecution.json

The following example explains the basic use of the PLSQLJob. It 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

...

...

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

...

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

...

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 for subsequent jobs. For example, the statement:

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

...

The output will be parsed by the regular expression:

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

that and will result in an order variable:

order_date = "2021-05-04"

...

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 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 to specify 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.

...

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 write to the stdout channel that is checked by the PLSQLJob for patterns indicating 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. that 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 e.g. in the output such as:

SET order_date IS 2021-05-04

The output will be parsed by the regular expression:

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

that and will result in an order variable:

$order_date = "2021-05-04"

...

Return Variables automatically available from the PLSQLJob

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

...

• 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 ParametersVariables
• JS7 - Job Resources
• JS7 - Use of Credential Store with JITL Jobs

...