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 JobSchedulerPLSQLJob.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 ('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="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"/> </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>
These jobs will be chained in a job chain
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).
When the second step is executed an email will be sent.
See also:
If you want to run SQLPLUS commands, you can use the SqlPlus-Job:
SOSSQLPlusJob.xml
If you are not using ORACLE and you want to execute a sql command, please see
Job JobSchedulerManagedDatabaseJob