ORACLE - How to run an Oracle Stored Procedure using PL SQL

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 description JobSchedulerManagedDatabaseJob.xml is stored in the ./jobs directory of your Job Scheduler installation.

The SQL instructions are written in the command parameter.
It is possible to define more than one instruction in the command parameter.
Such instructions 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 use 
 as newline.

Sample 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 ('The value of variable "vCounter" is: ['||vCounter||']');
    end if;
 END;

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

The xml configuration of the job

 <job  title="Execute PL/SQL procedure" order="no" 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>

Running this job will produce output in the log

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