Page History
...
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.
...
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
JITLjob class andcom.sos.jitl.jobs.db.oracle.PLSQLJobJava 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.
...
Name | Purpose | Required | Default Value | Example |
|---|---|---|---|---|
| PL/SQL statements to be executed | One of the these arguments has to be specified |
| |
command_script_file | PL/SQL statements from a file | /home/sos/some_script.sql | ||
| 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. |
| |
| User name for database access | scott | ||
| Password for database access | tiger | ||
hibernate_configuration_file | Replaces db_url, db_user, db_password arguments from a hibernate configuration file. | ./config/private/hibernate.cfg.xml | ||
| Regular expression to parse output from the | false | ^SETs+(\\s)\\s*ISs(.*)$ | |
credential_store_file | Location of a credential store database (*.kdbx) | false | ./config/private/jobs.kdbx | |
credential_store_key | Location of a credential store key file (*.key) | false | ./config/private/jobs.key |
...
This argument specifies the user account for the a database schema that is has been assigned the required privileges to execute the PL/SQL code.
...
The output will be parsed by the regular expression
^SETs+(\\s)\\s*ISs(.*)$
that and will result in an order variable
...
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:
...
The above example can be used:
- for PL/SQL code included as the value of the
commandargument. - for PL/SQL code included with a script file that is referenced by the
command_script_fileargument.
...
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 writing 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:
| Code Block | ||||
|---|---|---|---|---|
| ||||
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"
...
Return Variables automatically available from the PLSQLJob
The This job automatically creates the following order variables that are available to subsequent instructions and jobs.
...
Overview
Content Tools