Page History
...
The SOSPLSQLJob can be used to execute files that include PL/SQL statements.
...
Usage
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.
...
Arguments
The SOSPLSQLJob supports the following arguments:
Name | Description | MandatoryRequired | 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 |
...
| Tip |
|---|
Parameters can also be defined with following syntax:
Parameters are not case sensitive. |
| Warning |
PL/SQL
...
PL/SQL script as an External File
...
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job order="no" title="test">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<params >
<param name="testparam" value="test_command"/>
<param name="db_class" value="SOSOracleConnection"/>
<param name="db_driver" value="oracle.jdbc.driver.OracleDriver"/>
<param name="db_url" value="jdbc:oracle:thin:@8of9:1521:TEST"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
<param name="command" value="BEGIN INSERT INTO T_SOS_TEST VALUES ('\${testparam}'); END;"/>
</params>
<script language="java" java_class_path="" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Return
...
Variables created by the
...
SOSPLSQLJob
The JobScheduler job automatically creates the following order parameters, which will be variables that are available to subsequent job steps as order parametersinstructions and jobs.
Return Variable: sql_error
- The sql_error parameter contains all the error messages generated during the PL/SQL execution. This parameter will be empty if no errors occur.
Return Variable: std_out_output
- The std_out_output parameter contains all the messages spooled to stdout by PL/SQL.
...
Overview
Content Tools