Page History
...
The following example shows a basic example of the JobSchedulerPLSQLJobSOSPLSQLJob. It executes PL/SQL anonymous code blocks - selecting the current system date and displaying writing it on to stdout as order_date.
Arguments
The SOSPLSQLJob supports the following arguments:
Name | DescriptionPurpose | Required | 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 |
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job title="Execute PL/SQL procedure" order="no">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<description >
<include file="jobs/JobSchedulerPLSQLJob.xml"/>
</description>
<params >
<!-- Database connection parameters i.e. db_url, db_user, db_password -->
<include live_file="../common_settings/database/database_connection.params.xml" node=""/>
<!-- Parameter can be passed by task or as order param -->
<param name="date_mask" value="YYYYMMDD_HH24MI"/>
<!-- PL/SQL Code -->
<param name="command" value="
DECLARE
v_order_date VARCHAR2(16) := SYSDATE;
BEGIN
/* recommended to set variables in the PL/SQL is with ${SCHEDULER_PARAM_VARIABLE_NAME} */
SELECT to_char(SYSDATE, '\${SCHEDULER_PARAM_DATE_MASK}' )
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>
| ||||||
| Tip | ||||||
Parameters can also be defined with following syntax:
|
PL/SQL script as an External File
...
Overview
Content Tools