JS7 JobScheduler

Space shortcuts

Page tree

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 24 Next »

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.

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:

Name

Purpose

Required

Default Value

Example

command

PL/SQL statements to be executed

One of these arguments has to be specified


SELECT SYSDATE FROM DUAL

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

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



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


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 a database schema 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:

Passing variables to PL/SQL 
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 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:

Example 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

Example for a return variable
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 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. 

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

Further Resources


  • No labels