...

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 either:

...

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 to 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;

...

This argument specifies the user account for a database schema 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(.*)$

and will result in an order variable:

order_date = "2021-05-04"

...

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

...

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

...

• 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 put in set to the failed state.
• The cancel/killforce operation is applied to the order and to the job. The PLSQLJob will be killed 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 put in set to the failed state.The DBMS decides about rolling back transactions when closing the client session.

Further Resources

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

...