| 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.
...
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.