Page History

This article describes how to launch an Oracle 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 documentatin documentation of the job JobSchedulerPLSQLJob.xml JobSchedulerPLSQLJob can be found in the ./jobs directory of the JobScheduler installation.

The SQL commands are defined using the command parameter.
It is possible to define more than one commands in 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:

 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;

...

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

...

The xml configuration of the pl/sql job

 <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="scheduler`getDBPassword`"/>
    </params> 
    <script  language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
 </job>

Running this job will produce output in the log

 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.

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

...

See also:

If you want to run SQLPLUS commands SQL*Plus™ scripts, you can use the SqlPlus-Job:
SOSSQLPlusJob

If you are not using ORACLE Oracle™ RDBMS and/or you want to execute just a (simple) {{sql== command, please see
Job JobSchedulerManagedDatabaseJobthe  JobSchedulerManagedDatabaseJobSOSHibernate job.