Table of Contents | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
Description of JobSchedulerPLSQLJob - Execute PL/SQL procedure
What is the purpose of this job?
Introduction
The JobSchedulerPLSQLJob JITL job provides a standardized and parameterized interface for executing The JITL JobSchedulerPLSQLJob job provide an standardized and parameterized interface to execute Oracle PL/SQLs statements. The JobScheduler offers out of the box capability to execute PL/SQLs, pass passing parameters to the PL/SQL or collect collecting and passon passing on the results of a PL/SQL execution to next job step as a JobScheduler Order parameter. The JobSchedulerPLSQLJob can be used to execute existing PL/SQL files just by referring them in the command parameter.
A Simple JITL PL/SQL Job Example
Following is simple The following example shows a basic example of the JITL JobSchedulerPLSQLJob. Following job is simple example of executing an PL/SQL unanimous code bloc. Following example is selecting JobSchedulerPLSQLJob. It executes PL/SQL anonymous code blocks - selecting the current system date and displaying is it on stdout as a order_date.
Code Block | ||||||||
---|---|---|---|---|---|---|---|---|
| ||||||||
<?xml version="1.0" encoding="ISO-8859-1"?> <job title="Execute PL/SQL procedure" order="yes"> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <!-- Database connection parameters i.e. db_url, db_user, db_password --> <param name="db_url" value="jdbc:oracle:thin:@:1521:DORCL01"/> <param name="db_user" value="sos_scheduler"/> <param name="db_password" value="sos"/> <param name="command" value="<!-- PL/SQL Code --> <param name="command" value=" DECLARE v_order_date DATE := SYSDATE; BEGIN SELECT SYSDATE INTO v_order_date FROM DUAL; DBMS_OUTPUT.PUT_LINE(' +++ +++'); DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date); DBMS_OUTPUT.PUT_LINE(' +++ +++'); END; "/> <!-- dbms_output to JobScheduler Order parameter parser regex --> <param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/> </params> <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/> <run_time /> </job> |
...
Parameters
The JobSchedulerPLSQLJob requires the following parameters:
NameName | Title | Mandatory | Default | Example |
---|---|---|---|---|
command | PL/SQL statements to be executed | true |
|
|
db_url | JDBC connection string | true |
|
|
db_user | User name for database access | true |
| db username |
db_password | Password for database access | true |
| db password |
variable_parser_reg_expr | Regular expression to parse dbms_output and set order parameters for next job steps | false | ^SETs+(\\s)\\s*ISs(.*)$ | following line DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date); will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ will result in order parameter order_date="20140915" |
How is the PL/SQL script defined?
|
command
- The PL/SQL code can be:
saved to a separate file such as get_order.sql . This file can subsequently be referred to as the value of the "command" job parameter. This is a recommended approach for achieving "separation of concern" in application architecture.
Code Block language sql <param name="command" value="config/live/commn/sqls/get_order.sql"/>
PL/SQL code can also be specified as the value of the command parameter, with the entire PL/SQL being written as part of the Job.XML. This approach is preferred if the PL/SQL code is very small and only used by a single job.
saved to a separate file i.e. get_order.sql , and subsequently sql file can be referred as the value of the job parameter "command", this is a recommended approach to achieve "separation of concern" in application architecture.
Code Block language sql <param name="command" value="config/live/commn/sqls/get_order.sql"/>
PL/SQL code can also be specified as the value of the parameter command, entire PL/CODE can be written as part of the Job.XML, this approach is preferred
Code Block language sql <param name="command" value=" DECLARE DECLARE v_order_date DATE := SYSDATE; BEGIN SELECT SYSDATE INTO v_order_date FROM DUAL; DBMS_OUTPUT.PUT_LINE(' +++ +++'); DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date); DBMS_OUTPUT.PUT_LINE(' +++ +++'); END; "/>
- Before the script is executed, the script will be analyzed by the job.
- Should JobScheduler parameter names be containted in that script then they are substituted by their current value.
- The following notations are supported: %parameter_name%, ${SCHEDULER_PARAM_parameter_name}
How can results be used in subsequent jobs?
- The job makes use of the appropriate parameters in the order.
- The first of these are static parameters, such as those described in the section "return parameters" of the template documentation.
- At the same time these are dynamic parameters. The job anlyzes the output of the PL/SQL statements and extracts values by use of a regular expression that catches both names and values of these parameters. The regular expression is defined by the parameter variable_parser_reg_exp.
...
db_url
JITL needs a standard JDBC database connection string such as jdbc:oracle:thin:@localhost:1521:XE
db_user
DB Username which has necessary database permission for executing the PL/SQL code.
db_password
The password for the DB user defined in the db_user parameter.
variable_parser_reg_expr
This parameter defines a regular expression for parsing the dbms_output from the PL/SQL execution and sets the order parameters for subsequent job steps. For example, the dbms ouput DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date) displays the output on console SET order_date is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ will result as order parameter order_date="20140915".
Saving Database Connection Settings in a Parameter File
It strongly recommend that a db_connection parameter file such as database_connection.parameter.xml is used to store all the database connection settings in a common location. This approach enables the user to manage settings at central location which can then be reused by multiple jobs.
This approach also makes it easy to maintain different settings for development, integration and production environments.
The following shows an example database connection parameter file:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<params > | ||||||
Code Block | ||||||
| ||||||
declare howmany NUMBER; p_id varchar2(20) := null; <param name="db_url" begin value="jdbc:oracle:thin:@:1521:DORCL01"/> dbms_output.put_line('set variable1=value1'); <param p_id := '12345'; --name="db_user" value="sos_scheduler"/> -- If the<param name="db_password" value="sos"/> <params > |
The next example shows a JITL job where the database connection parameters are stored in an external file. In this example a "common_settings/database" directory has been created inside the JobScheduler's live folder.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?> <job title="Execute PL/SQL procedure" order="no"> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <!-- Database connection parameters i.e. db_url, db_user, db_password --> <include live_file="../common_settings/database/database_connection.params.xml" node=""/> <!-- PL/SQL Code -->job or order contains the parameter -- -- table_name=scheduler_variables -- -- then all following selects are substituted to 'select count(*) into howmany from scheduler_variables;' -- select count(*) into howmany from $\{SCHEDULER_PARAM_table_name\}; select count(*) into howmany from %table_name%; select count(*) into howmany from %TABLE_NAME%; select count(*) into howmany from $\{SCHEDULER_PARAM_TABLE_NAME\}; select count(*)<param into howmanyname="command" from $\{scheduler_param_table_name\}; value=" DECLARE select count(*) into howmany from $\{sChEdUlEr_pArAm_tAbLe_nAmE\}; -- v_order_date DATE := SYSDATE; BEGIN -- now put the results to the buffer -- JS will get the results from the buffer SELECT SYSDATE INTO v_order_date FROM DUAL; DBMS_OUTPUT.PUT_LINE(' +++ -- dbms_output.put_line('The table %table_name% has ' || howmany || ' rows.'); dbms_output.put_line('set howmany is ' || howmany); +++'); DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date); DBMS_OUTPUT.PUT_LINE(' +++ dbms_output.put_line('set variable1 is ' || p_id+++'); dbms_output.put_line('set variable2 is value2'); end; |
Example: PL/SQL code
- If the job or order contains the parameter table_name with the value
scheduler_variables
then all following SQL "select" statements are substituted to:select count(*) into howmany from scheduler_variables
; - If the job is started in a job chain by an order then the following parameters are added to the order
- see the parameter variable_parser_reg_expr
- For this purpose, the output of the PL/SQL statement is parsed with the regular expression:
^SET\\s+([^\\s]+)\\s*IS\\s+(.*)$
From the above example the following parameters are returned:
howmany=8
variable1=12345
variable2=value2
If the same parameter is set multiple times then the last value is used as the order parameter.
An example for a job xml file:
END;
"/>
<!-- dbms_output to JobScheduler Order parameter parser regex -->
<param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
</params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Passing parameters to the PL/SQL
JobScheduler order parameters can be passed to the PL/SQL. PL/SQL code can be parameterized by defining variables such as ${SCHEDULER_PARAM_VARIABLE_NAME}. Variables can be set using environment variables, JobScheduler task parameters ( as described in the following example) or from JobScheduler order parameters.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job title="Execute PL/SQL procedure" order="no">
<settings | ||||||
Code Block | ||||||
| ||||||
<job> <params> <param name="command" value="select sysdate from dual" /> <param name="db_url" value="jdbc:oracle:thin:@localhost:1521:XE" /><log_level ><![CDATA[debug9]]></log_level> </settings> <param name="db_user" value="test" / <description > <param<include namefile="db_password" value="test" />jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <param name="variable_parser_reg_expr" value="^SET\\s+([^\\s]+)\\s*IS\\s+(.*)$" / <!-- Database connection parameters i.e. db_url, db_user, db_password --> </params> <include <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass" /> </job> |
Parameters used by JobSchedulerPLSQLJob
...
Name | Title | Mandatory | Default | Example |
---|---|---|---|---|
PL/SQL statements to be executed | false |
| select sysdate from dual | |
JDBC connection string | false |
| jdbc:oracle:thin:@localhost:1521:XE | |
User name for database access | false |
| ||
Password for database access | false |
| ||
Regular expression for parameter parsing | false | ^SET |
live_file="../common_settings/database/database_connection.params.xml" node=""/>
<!-- Parameter can be passed by task or as order param -->
<param name="date_mask" value="YYYYMMDD_HH24MI"/>
<!-- PL/SQL Code -->
<param name="command" value="
DECLARE
v_order_date VARCHAR2(16) := SYSDATE;
BEGIN
/* recommended to set variables in the PL/SQL is with ${SCHEDULER_PARAM_VARIABLE_NAME} */
SELECT to_char(SYSDATE, '\${SCHEDULER_PARAM_DATE_MASK}' )
INTO v_order_date
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(' +++ +++');
DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date);
DBMS_OUTPUT.PUT_LINE(' +++ +++');
END; "/>
<!-- dbms_output to JobScheduler Order parameter parser regex -->
<param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
</params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Tip |
---|
Parameters can also be defined with following syntax:
Parameters are not case sensitive. |
Warning |
---|
When PL/SQL code is part of Job XML file, then parameters should be defined in the form \${SCHEDULER_PARAM_PARAMETER_NAME}. If PL/SQL code is read from file system, the parameter can be defined without the "\" |
PL/SQL script as an External File
PL/SQL code can be defined directly inside the Job xml as a command parameter value but is generally better stored on the file system. JITL jobs can be configured to read PL/SQL scripts from the file system by defining the script path as a value for the command parameter i.e.
In the following example the PL/SQL code is saved to the filesystem in C:\app\executables\plsql\get_order_date.sql and subsequently referenced using the command parameter.
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job title="Execute PL/SQL procedure" order="no">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<description >
<include file="jobs/JobSchedulerPLSQLJob.xml"/>
</description>
<params >
<!-- Database connection parameters i.e. db_url, db_user, db_password -->
<include live_file="../common_settings/database/database_connection.params.xml" node=""/>
<!-- Parameter can be passed by task or as order param -->
<param name="date_mask" value="YYYYMMDD_HH24MI"/>
<!-- PL/SQL script from filesystem -->
<param name="command" value="C:/app/executables/plsql/get_order_date.sql"/>
<!-- dbms_output to JobScheduler Order parameter parser regex -->
<param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/>
</params>
<script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Passing PL/SQL results to subsequent job steps as parameters
JobScheduler jobs can create and update JobScheduler Order parameters. The JobSchedulerPLSQLJob can also pass on the result of PL/SQL execution i.e. calculated dates, parameters calculated from tables, etc. By default the JobSchedulerPLSQL job defines a regular expression to parse dbms_output from the execution of PL/SQLs and sets order parameters for subsequent job steps. For example, the DBMS_OUTPUT.PUT_LINE('SET order_date IS '|| v_order_date) dbms ouput displays the output on console; if SET order_date is 20140915, it will be parsed by regular expression ^SETs+(\\s)\\s*ISs(.*)$ and return the order_date="20140915" order parameter. All dbms_output statements matching the ^SETs+(\\s)\\s*ISs(.*)$ regular expression will be set as order_parameters.
Advanced Configuration
Generic job for executing multiple PL/SQLs
The JobSchedulerPLSQLJob can be configured as a generic node inside a job chain and executable PL/SQL script can be defined as an order parameter. The following example shows such a generic job. The job chain has a job node- execute_plsql - two orders - get_order_date and get_last_booking_date. Each order is scheduled to be executed at a different time. Both the orders are configured to use a different PL/SQL script file i.e. get_order_date.sql and get_last_booking_date.sql.
JobChain
Code Block language xml title JITL-PLSQL.job_chain.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <job_chain orders_recoverable="yes" visible="yes"> <job_chain_node state="execute_plsql" job="JITL-PLSQL" next_state="sucess" error_state="error"/> <job_chain_node state="sucess"/> <job_chain_node state="error"/> </job_chain>
Job
Code Block language xml title JITL-PLSQL.job.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <job title="Execute PL/SQL procedure" order="yes"> <settings > <log_level ><![CDATA[debug9]]></log_level> </settings> <description > <include file="jobs/JobSchedulerPLSQLJob.xml"/> </description> <params > <!-- Parameter can be passed by task or as order param --> <param name="date_mask" value="YYYYMMDD_HH24MI"/> <!-- Database connection parameters i.e. db_url, db_user, db_password --> <include live_file="../common_settings/database/database_connection.params.xml" node=""/> <!-- dbms_output to JobScheduler Order parameter parser regex --> <param name="variable_parser_reg_expr" value="^SET\s+([^\s]+)\s*IS\s+(.*)$"/> </params> <script language="java" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/> <run_time /> </job>
Order : get_order_date
Code Block language xml title JITL-PLSQL,get_order_date.order.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <order title="Calculate Order Date"> <params > <!-- PL/SQL script file --> <param name="command" value="C:/app/executables/plsql/get_last_booking_date.sql"/> </params> <run_time let_run="no"> <period single_start="08:00"/> </run_time> </order>
Order : get_last_booking_date
Code Block language xml title JITL-PLSQL,get_last_order_date.order.xml collapse true <?xml version="1.0" encoding="ISO-8859-1"?> <order title="Calculate last booking date"> <params > <!-- PL/SQL script file --> <param name="command" value="C:/app/executables/plsql/get_last_booking_date.sql"/> </params> <run_time let_run="no"> <period single_start="11:00"/> </run_time> </order>
Standalone PL/SQL Jobs
If PL/SQL code needs to be parameterized by a job parameter the syntax for parameter substitute is different compare to order jobs.
Since the syntax of suffixing an order parameter for SCHEDULER_PARAM is not required the parameter name can directly be substituted in the PL/SQL code.
See the following example for two variant for standalone PL/SQL code.
PL/SQL Code as script
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job order="no" title="test">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<params >
<param name="testparam" value="test"/>
<param name="db_class" value="SOSOracleConnection"/>
<param name="db_driver" value="oracle.jdbc.driver.OracleDriver"/>
<param name="db_url" value="jdbc:oracle:thin:@8of9:1521:TEST"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
</params>
<script language="java" java_class_path="" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass">
<![CDATA[
BEGIN
INSERT INTO T_SOS_TEST VALUES ('${testparam}');
END;
]]>
</script>
<run_time />
</job>
|
PL/SQL Code as command
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
<?xml version="1.0" encoding="ISO-8859-1"?>
<job order="no" title="test">
<settings >
<log_level ><![CDATA[debug9]]></log_level>
</settings>
<params >
<param name="testparam" value="test_command"/>
<param name="db_class" value="SOSOracleConnection"/>
<param name="db_driver" value="oracle.jdbc.driver.OracleDriver"/>
<param name="db_url" value="jdbc:oracle:thin:@8of9:1521:TEST"/>
<param name="db_user" value="scheduler"/>
<param name="db_password" value="scheduler"/>
<param name="command" value="BEGIN INSERT INTO T_SOS_TEST VALUES ('\${testparam}'); END;"/>
</params>
<script language="java" java_class_path="" java_class="sos.scheduler.db.JobSchedulerPLSQLJobJSAdapterClass"/>
<run_time />
</job>
|
Return parameters created by the JobSchedulerPLSQLJob
The JobScheduler automatically creates the following order parameters, which will be available to subsequent job steps as order parameters.
sql_error
- The sql_error parameter contains all the error messages generated during the PL/SQL execution. This parameter will be empty if no errors occur.
std_out_output
- The std_out_output parameter contains all the messages spooled to stdout by PL/SQL.
See also:
- How to work with PL/SQL and the Oracle DBMS
- How to run Oracle Stored Procedures using PL/SQL
- The JobSchedulerPLSQLJob JITL job documentation
Parameter: command
- It is possible to define more than one PL/SQL statement in the command parameter. Such statements are then carried out in the order in which they are specified and have to be separated by a semicolon and a subsequent new line.
- Parameters are substituted in database statements. This is effected by using parameter names in the form
§{param}
at any given position in a statement. The parameter is then substituted by the value of the job parameter with the same name. Substitution takes place before database statements are executed.
Parameter: variable_parser_reg_expr
- The output of the PL/SQL statement will be parsed by this regular expression. The matches are available as order parameters.
- The regular expression has to specify to two groups: the first group for the parameter name and the second group for the value.
The prompt statement in SQL*Plus writes some output like this:
Code Block | ||||
---|---|---|---|---|
| ||||
WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT FAILURE
prompt ready;
prompt foo bar;
prompt SET hello IS world;
exit; |
The dbms_output.put_line() function in PL/SQL writes some output like this:
Code Block | ||||
---|---|---|---|---|
| ||||
begin
dbms_output.put_line('ready');
dbms_output.put_line('Set hello IS world');
dbms_output.put_line('fooh1. bar');
end; |
Return parameters created by JobSchedulerPLSQLJob
The order parameters described below are automatically created and returned by the job:
...
Name | Title | Mandatory | Default |
---|---|---|---|
Error messages of the client or the server |
|
| |
Output to stdout |
|
Parameter: sql_error
- If during the execution of the PL/SQL statements one or multiple errors occur, then the error message will be stored in this order parameter. If no error occurs then the contents of the parameter will be empty.
Parameter: std_out_output
...