JS7 JobScheduler

Space shortcuts

Page tree

Versions Compared

Old Version 22

changes.mady.by.user Alan Amos

Saved on

compared with

New Version Current

changes.mady.by.user Andreas Püschel

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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
      serverSOS JIRA
      columnIdsissuekey,summary,issuetype,created,updated,duedate,assignee,reporter,priority,status,resolution
      columnskey,summary,type,created,updated,due,assignee,reporter,priority,status,resolution
      serverId6dc67751-9d67-34cd-985b-194a8cdc9602
      keyJITL-743

Usage

When defining the job either:

...

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

...

Name

Purpose

Required

Default Value

Example

command

PL/SQL statements to be executed

One of these arguments has to be specified


SELECT SYSDATE FROM DUALDECLARE
  return_cursor SYS_REFCURSOR;
BEGIN
  OPEN return_cursor FOR 'SELECT FIRST_NAME, LAST_NAME, AGE FROM EMPLOYEES';
END;

command_script_filePL/SQL statements from a file
/home/sos/some_script.sql

db_url

JDBC connection string

Either a DB URL, location of a Hibernate configuration file or a Credential Store reference is used.

User and password for database access can be specified by arguments with the same name and can be omitted if an Oracle Wallet is used or can be specified with the Hibernate configuration file, from a Job Resource or from a Credential Store Reference.


jdbc:oracle:thin:@localhost:1521:XE

db_user

User name for database access


scott

db_password

Password for database access


tiger
hibernate_configuration_fileReplaces db_url, db_user, db_password arguments from a hibernate configuration file.
./config/private/hibernate.cfg.xml

variable_parser_reg_expr

Regular expression to parse output from the DBMS_OUTPUT package and to set order variables for next jobs

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



result_set_as_variables


Specifies how a result set is forwarded to order variables for subsequent jobs:


  • false: no result set is processed (default)
  • column_value: for the first record of the result set from each column name a variable is created that holds the column value.
  • name_value:  the first two columns of the result set will be added to order variables. The values of the first column of each record included with the result set will become the variable names, the values of the second column will become the variable values. 


  • csv: the column names will be written to the first line of the file specified with the result_file argument, any column values of the result set will be written to subsequent lines of this file. Column names and values will be enclosed with double quotes and will be separated by commas.
    • Example:
    • "FIRST_NAME","LAST_NAME","AGE"
      "John","Doe","30"
  • xml: rows are created as a sequence of <ROW> elements of the <RESULTSET> root element. The column names will be created as XML element names. XML output is written to the file specified with the result_file argument. 
    • Example:

    • <?xml version="1.0" encoding="UTF-8"?>
      <RESULTSET>
        <ROW>
          <FIRST_NAME><![CDATA[John]]></FIRST_NAME>
          <LAST_NAME><![CDATA[Doe]]></LAST_NAME>
          <AGE><![CDATA[30]]></AGE>
        </ROW>
      </RESULTSET>

    • json: the column names will be written as an array of JSON objects to the file specified with the result_file argument, any column values of the result set will be written as object properties to this file. 
      • Example:

      • [
          {
            "FIRST_NAME" : "John",
            "LAST_NAME" : "DOE",
            "AGE" : 30
          }
        ]

no

result_fileSpecifies the path to a result file if the resultset_as_variables argument is used with one of the values csv, xml, json. An absolute path can be specified and a relative path can be used that starts from the Agent's working directory.no

credential_store_fileLocation of a credential store database (*.kdbx)false
./config/private/jobs.kdbx
credential_store_keyLocation of a credential store key file (*.key)false
./config/private/jobs.key

...

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:

Code Block
languagesql
titlePL/SQL procedure returning parsable output
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.

Code Block
languagesql
titlePL/SQL procedure returning result set cursor
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 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"

...

Code Block
languagexml
titlePassing variables to PL/SQL
linenumberstrue
collapsetrue
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;

...

Code Block
languagesql
titleExample how to specify a return variable
EXEC DBMS_OUTPUT.PUT_LINE( 'SET order_date IS ' || TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') );

results e.g. in the output such as:

Code Block
languagesql
titleExample for a return variable
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 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

...