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]