Page History
...
The following example shows a basic example of the JobSchedulerPLSQLJob. It executes PL/SQL anonymous code blocks - selecting the current system date and displaying it on stdout as order_date.
| Code Block | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job title="Execute PL/SQL procedure" order="yes">
<description >
<include file="jobs/JobSchedulerPLSQLJob.xml"/>
</description>
<params >
<!-- Database connection parameters i.e. db_url, db_user, db_password -->
<param name="db_url" value="jdbc:oracle:thin:@:1521:DORCL01"/>
<param name="db_user" value="sos_scheduler"/>
<param name="db_password" value="sos"/>
<!-- PL/SQL Code -->
<param name="command" value="
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;
"/>
<!-- dbms_output to JobScheduler Order parameter parser regex -->
<param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
</params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Parameterization
The SOSPLSQLJob supports the following arguments:
Parameterization
The SOSPLSQLJob supports the following arguments:
Name | Description | Mandatory | Default Value | Example | |||
|---|---|---|---|---|---|---|---|
| PL/SQL statements to be executed | One of the arguments has | |||||
Name | Description | Mandatory | Default Value | Example | |||
| PL/SQL statements to be executed | One of the 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 or a Hibernate configuration file is used. User and password for database access can be specified by arguments with the same name, can be omitted if Oracle Wallet is used or can be specified with the Hibernate configuration file |
| ||||
| User name for database access | scott | |||||
| Password for database access | tiger | |||||
hibernate_configuration_file | Replaces db_url, db_user, db_password from a hibernate configuration file. | ./config/private/hibernate.cfg.xml | |||||
| 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_file | Location of a credential store file (*.kdbx) | false | ./config/private/jobs.kdbx | credential_store_key | Location of a credential store file (*.key) | false | ./config/private/jobs.key |
Argument: command
...
saved to a separate file such as get_order.sql . This file can subsequently be referred to as the value of the "command" job parameter. This is a recommended approach for achieving "separation of concern" in application architecture.
| ) | false | ./config/private/jobs.kdbx | ||
credential_store_key | Location of a credential store file (*.key) | false | ./config/private/jobs.key |
Argument: command
...
| language | sql |
|---|
...
PL/SQL code can
...
be specified as the value of the command
...
argument:
| Code Block | |
|---|---|
|
...
| ||
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
The PL/SQL code could be used from from a file that is assigned this argument:
| Code Block | ||
|---|---|---|
| ||
./config/get_order.sql |
Argument: db_url
JITL needs The job requires a standard JDBC database connection string such as jdbc:oracle:thin:@localhost:1521:XE
Argument: db_user
DB Username which has necessary database permission for executing 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
The This argument specifies the password for the DB user defined in account given with the db_user parameter argument.
Argument: variable_parser_reg_expr
This argument specifies a regular expression for parsing of 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') );
displays writes the following output to the console:
...
order_date = "2021-05-04"
Subsequent jobs can make use of the $order_date variable.
Saving Database Connection Settings in a Parameter File
...
Overview
Content Tools