Skip to end of metadata
Go to start of metadata

Introduction 

The JITL PLSQLJob provides a parameterizable interface for executing Oracle® PL/SQL statements. The JS7 offers out-of-the-box capabilities

  • to execute PL/SQL,
  • to pass arguments to PL/SQL
  • to collect and to pass on the results of PL/SQL execution to next jobs.

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

Usage

When defining the job consider

  • to select the JITL job class and
  • to specify the com.sos.jitl.jobs.db.oracle.PLSQLJob Java class name.

The following example shows a basic example of the PLSQLJob. It executes PL/SQL anonymous code blocks - selecting the current system date and writing it to stdout.

Arguments

The PLSQLJob supports the following arguments:

Name

Purpose

Required

Default Value

Example

command

PL/SQL statements to be executed

One of the 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 or a Hibernate configuration file is used.

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


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 file (*.kdbx)false
./config/private/jobs.kdbx
credential_store_keyLocation of a credential store file (*.key)false
./config/private/jobs.key

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 the database schema that is 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(.*)$

that 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 to state ${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 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 to write to the stdout channel that is checked by the PLSQLJob for patterns indicating return values. The output is parsed by the PLSQLJob using a regular expression that is specified by the argument variable_parser_reg_expr. that 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(.*)$

that 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

The 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 stdout by PL/SQL. 

Further References



Write a comment…