Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
This article describes how to launch an Oracle™ RDBMS stored procedure using the JobSchedulerPLSQLJob JITL Job JobSchedulerPLSQLJob. This job is used standalone or triggered by orders to execute pl/sql statements in a database. These can be stored procedures or SQL statements as well.
The example also shows, how to send the result (output) of an pl/sql job as an email.
...
The SQL commands are defined using the command parameter.
It is possible to define more than one command as value of the command parameter.
Such commands are then carried out in the order in which they are written and must be separated by a semicolon and a subsequent new line.
You can have to use the character sequence
as for a newline.
Example of a stored procedure:
Code Block |
---|
CREATE OR REPLACE PROCEDURE myTestProc IS
vCounter NUMBER := 0;
BEGIN
select count(*) into vCounter from SCHEDULER_HISTORY;
if vCounter>0 then
dbms_output.put_line ('Set plsql_result IS The value of variable "vCounter" is: ['||vCounter||']');
end if;
END;
|
...
Code Block | ||
---|---|---|
| ||
<job title="Execute PL/SQL procedure" order="yes" name="TestSQL">
<params >
<param name="command" value="begin myTestProc; end;"/>
<param name="db_url" value="jdbc:oracle:thin:@ur-lAsss:1521:XE"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="`getDBPassword`"/>
</params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
</job>
|
Running this job will produce output in the log
Code Block |
---|
2013-04-03 10:29:21.477 [info] Set plsql_result IS The value of variable "vCounter" is: [5495]
|
...
Code Block | ||
---|---|---|
| ||
<job title="Send Mails" order="yes" stop_on_error="no" name="SendMail">
<description >
<include file="jobs/JobSchedulerManagedMailJob.xml"/>
</description>
<params >
<param name="to" value="info@sos-berlin.com"/>
<param name="subject" value="Result from oracle"/>
<param name="host" value="smtp_host"/>
</params>
<script language="java" java_class="sos.scheduler.managed.JobSchedulerManagedMailJob"/>
<monitor name="configuration_monitor" ordering="0">
<script java_class="sos.scheduler.managed.configuration.ConfigurationOrderMonitor" language="java"/>
</monitor>
<run_time />
</job>
|
...
If you are not using Oracle™ RDBMS and/or you want to execute just a (simple) {{sqh1. sql== command, please see
Job JobSchedulerManagedDatabaseJobthe JobSchedulerManagedDatabaseJobSOSHibernate job.