Skip to end of metadata
Go to start of metadata

This article describes how to launch an Oracle™ RDBMS stored procedure using the JobSchedulerPLSQLJob JITL Job. 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 documentation of the job 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 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 have to use the character sequence 
 for a newline.

Example of a stored procedure:

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 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 following is an example of a job in which the command contains one statement.

The xml configuration of the pl/sql job

Running this job will produce output in the log

The SendEmail job comes from the JITL library.

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 SQL*Plus™ scripts, you can use the SqlPlus-Job: SOSSQLPlusJob

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