Page History

...

This article describes how to launch an Oracle stored procedure using JITL Job JobSchedulerPLSQLJob. This job is used as a standalone solution or triggered by orders to execute pl/sql statements in a database. These can be database procedures or SQL statements.

The sample also shows, how to send the result of an pl/sql job as email.

The description JobSchedulerManagedDatabaseJob.xml is stored in the ./jobs directory of your Job Scheduler installation.

...

 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;

Please note that the output begins with "Set plsql_result" This will create an order parameter which can be used for example in the body of an email.

The sample defines a job chain with two steps. First step is executing the pl/sql job and the second step is sending an email

The following is an example of a job in which the command contains one statement.

...

The xml configuration of the pl/sql job

 <job  title="Execute PL/SQL procedure" order="noyes" 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="scheduler"/>
    </params> 
    <script  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
 </job>

...

 2013-04-03 10:29:21.477 [info]   Set plsql_result IS The value of variable "vCounter" is: [5495]

The SendEmail job comes from the JITL library.

Image Added

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

These jobs will be chained in a job chain

Image Added

The second step of the job chain defines a node parameter for the body (the other parameters like subject, smtp-server are defined as job parameters).

Image Added

When the second step is executed an email will be sent.
Image Added